| Groups | Blog | Home |
sql server programming : Column with runing values
select (select count (*) from authors b where b.au_lname < a.au_lname or (b.au_lname = a.au_lname and (b.au_fname < a.au_fname or (b.au_fname = a.au_fname and b.au_id <= a.au_id)))) as mycolumn , au_id,au_lname,au_fname from authors a order by au_lname, au_fname -- Tom --------------------------------------------------------------- Thomas A. Moreau, BSc, PhD, MCSE, MCDBA SQL Server MVP Columnist, SQL Server Professional Toronto, ON Canada www.pinnaclepublishing.com/sql [quoted text, click to view] "Naveed Akbar" <naveedch@magic.net.pk> wrote in message Hi all,news:#cFlxUwzDHA.4060@TK2MSFTNGP11.phx.gbl... Its great to see that so many ppl helpout each other. I hope I can do the same on day :), anyway Can any one please guide me that how can i make a runing column in result set e.g. i have a query select au_id,au_lname,au_fname from authors which returns au_id au_lname au_fname ----------- -------------------------------- -------- ----------- --------- 409-56-7008 Bennet Abraham 648-92-1872 Blotchet-Halls Reginald 238-95-7766 Carson Cheryl 722-51-5454 DeFrance Michel 712-45-1867 del Castillo Innes 427-17-2319 Dull Ann 213-46-8915 Green Marjorie how can i add another column which shows mycolumn au_id au_lname au_fname ----------- ----------- ---------------------------------------- ----------- --------- 1 409-56-7008 Bennet Abraham 2 648-92-1872 Blotchet-Halls Reginald 3 238-95-7766 Carson Cheryl 4 722-51-5454 DeFrance Michel 5 712-45-1867 del Castillo Innes 6 427-17-2319 Dull Ann 7 213-46-8915 Green Marjorie I would really apericate the help Regards
Although Tom's solution works (and is verrrry interesting),
running this on a relatively small table in my database takes about 10 times as long as one without the "count" column. This is best done client side. Very easy to use a counter and generate this. [quoted text, click to view] "Naveed Akbar" <naveedch@magic.net.pk> wrote in message news:%23cFlxUwzDHA.4060@TK2MSFTNGP11.phx.gbl... > Hi all, > > Its great to see that so many ppl helpout each other. I hope I can do the > same on day :), anyway Can any one please guide me that how can i make a > runing column in result set e.g. > > i have a query > > select au_id,au_lname,au_fname from authors > > which returns > > au_id au_lname > au_fname > ----------- -------------------------------- -------- --------- -- > --------- > 409-56-7008 Bennet Abraham > 648-92-1872 Blotchet-Halls Reginald > 238-95-7766 Carson Cheryl > 722-51-5454 DeFrance Michel > 712-45-1867 del Castillo Innes > 427-17-2319 Dull Ann > 213-46-8915 Green Marjorie > > > how can i add another column which shows > > mycolumn au_id au_lname au_fname > ----------- ----------- ---------------------------------------- --------- -- > --------- > 1 409-56-7008 Bennet Abraham > 2 648-92-1872 Blotchet-Halls Reginald > 3 238-95-7766 Carson Cheryl > 4 722-51-5454 DeFrance Michel > 5 712-45-1867 del Castillo Innes > 6 427-17-2319 Dull Ann > 7 213-46-8915 Green Marjorie > > I would really apericate the help > > > Regards > > Naveed Akbar > >
Hey, I'm the first to agree. It really is a front-end job to do, but hey
when you have a hammer - SQL - everything looks like a nail. ;-) -- Tom --------------------------------------------------------------- Thomas A. Moreau, BSc, PhD, MCSE, MCDBA SQL Server MVP Columnist, SQL Server Professional Toronto, ON Canada www.pinnaclepublishing.com/sql [quoted text, click to view] "Raymond D'Anjou (raydan)" <raydan@canatrade.nospamcom> wrote in message Although Tom's solution works (and is verrrry interesting),news:OyDpD1wzDHA.3468@TK2MSFTNGP11.phx.gbl... running this on a relatively small table in my database takes about 10 times as long as one without the "count" column. This is best done client side. Very easy to use a counter and generate this. [quoted text, click to view] "Naveed Akbar" <naveedch@magic.net.pk> wrote in message
news:%23cFlxUwzDHA.4060@TK2MSFTNGP11.phx.gbl... > Hi all, > > Its great to see that so many ppl helpout each other. I hope I can do the > same on day :), anyway Can any one please guide me that how can i make a > runing column in result set e.g. > > i have a query > > select au_id,au_lname,au_fname from authors > > which returns > > au_id au_lname > au_fname > ----------- -------------------------------- -------- --------- -- > --------- > 409-56-7008 Bennet Abraham > 648-92-1872 Blotchet-Halls Reginald > 238-95-7766 Carson Cheryl > 722-51-5454 DeFrance Michel > 712-45-1867 del Castillo Innes > 427-17-2319 Dull Ann > 213-46-8915 Green Marjorie > > > how can i add another column which shows > > mycolumn au_id au_lname au_fname > ----------- ----------- ---------------------------------------- --------- -- > --------- > 1 409-56-7008 Bennet Abraham > 2 648-92-1872 Blotchet-Halls Reginald > 3 238-95-7766 Carson Cheryl > 4 722-51-5454 DeFrance Michel > 5 712-45-1867 del Castillo Innes > 6 427-17-2319 Dull Ann > 7 213-46-8915 Green Marjorie > > I would really apericate the help > > > Regards > > Naveed Akbar >
LOL. More like a sqledgehammer.
[quoted text, click to view] "Tom Moreau" <tom@dont.spam.me.cips.ca> wrote in message = Hey, I'm the first to agree. It really is a front-end job to do, but =news:e2TO$6wzDHA.716@TK2MSFTNGP12.phx.gbl... hey when you have a hammer - SQL - everything looks like a nail. ;-) --=20 Tom --------------------------------------------------------------- Thomas A. Moreau, BSc, PhD, MCSE, MCDBA SQL Server MVP Columnist, SQL Server Professional Toronto, ON Canada
Heh, heh, heh... :-)
-- Tom --------------------------------------------------------------- Thomas A. Moreau, BSc, PhD, MCSE, MCDBA SQL Server MVP Columnist, SQL Server Professional Toronto, ON Canada www.pinnaclepublishing.com/sql [quoted text, click to view] "Raymond D'Anjou (raydan)" <raydan@canatrade.nospamcom> wrote in message Hey, I'm the first to agree. It really is a front-end job to do, but heynews:e9JJ9exzDHA.1676@TK2MSFTNGP12.phx.gbl... LOL. More like a sqledgehammer. "Tom Moreau" <tom@dont.spam.me.cips.ca> wrote in message news:e2TO$6wzDHA.716@TK2MSFTNGP12.phx.gbl... when you have a hammer - SQL - everything looks like a nail. ;-) -- Tom --------------------------------------------------------------- Thomas A. Moreau, BSc, PhD, MCSE, MCDBA SQL Server MVP Columnist, SQL Server Professional Toronto, ON Canada
[quoted text, click to view]
"Raymond D'Anjou (raydan)" <raydan@canatrade.nospamcom> wrote in message news:OyDpD1wzDHA.3468@TK2MSFTNGP11.phx.gbl... > Although Tom's solution works (and is verrrry interesting), > running this on a relatively small table in my database > takes about 10 times as long as one without the "count" column. I'm shocked,shocked! :~) [quoted text, click to view] > This is best done client side. This is why other enterprise databases have sql99 OLAP functions to do this kind of thing.(You can even do this in MySql without that hideous ancient subquery:).If Yukon doesn't have them you can still stick to your point, otherwise prepare to eat some crow:~) Happy New Year, OBD
It's no surprise, that a
K_Large * O(n*n) process is slower than a K_Small * O(n) process. Meanwhile, K_Large can become K_Reduced via: create index HowAboutMe on authors(au_lname, au_fname, au_id) and furthere reduced to K_FurtherReduced via: create unique clustered index HowAboutMe on authors(au_lname, au_fname, au_id) Bye, Delbert Glass [quoted text, click to view] "Raymond D'Anjou (raydan)" <raydan@canatrade.nospamcom> wrote in message news:OyDpD1wzDHA.3468@TK2MSFTNGP11.phx.gbl... > Although Tom's solution works (and is verrrry interesting), > running this on a relatively small table in my database > takes about 10 times as long as one without the "count" column. > This is best done client side. > Very easy to use a counter and generate this. > > "Naveed Akbar" <naveedch@magic.net.pk> wrote in message > news:%23cFlxUwzDHA.4060@TK2MSFTNGP11.phx.gbl... > > Hi all, > > > > Its great to see that so many ppl helpout each other. I hope I can do the > > same on day :), anyway Can any one please guide me that how can i make a > > runing column in result set e.g. > > > > i have a query > > > > select au_id,au_lname,au_fname from authors > > > > which returns > > > > au_id au_lname > > au_fname > > ----------- -------------------------------- -------- --------- > -- > > --------- > > 409-56-7008 Bennet Abraham > > 648-92-1872 Blotchet-Halls Reginald > > 238-95-7766 Carson Cheryl > > 722-51-5454 DeFrance Michel > > 712-45-1867 del Castillo Innes > > 427-17-2319 Dull Ann > > 213-46-8915 Green Marjorie > > > > > > how can i add another column which shows > > > > mycolumn au_id au_lname au_fname > > ----------- ----------- ---------------------------------------- --------- > -- > > --------- > > 1 409-56-7008 Bennet Abraham > > 2 648-92-1872 Blotchet-Halls Reginald > > 3 238-95-7766 Carson Cheryl > > 4 722-51-5454 DeFrance Michel > > 5 712-45-1867 del Castillo Innes > > 6 427-17-2319 Dull Ann > > 7 213-46-8915 Green Marjorie > > > > I would really apericate the help > > > > > > Regards > > > > Naveed Akbar > > > > > >
Hi all,
Its great to see that so many ppl helpout each other. I hope I can do the same on day :), anyway Can any one please guide me that how can i make a runing column in result set e.g. i have a query select au_id,au_lname,au_fname from authors which returns au_id au_lname au_fname ----------- -------------------------------- -------- ----------- --------- 409-56-7008 Bennet Abraham 648-92-1872 Blotchet-Halls Reginald 238-95-7766 Carson Cheryl 722-51-5454 DeFrance Michel 712-45-1867 del Castillo Innes 427-17-2319 Dull Ann 213-46-8915 Green Marjorie how can i add another column which shows mycolumn au_id au_lname au_fname ----------- ----------- ---------------------------------------- ----------- --------- 1 409-56-7008 Bennet Abraham 2 648-92-1872 Blotchet-Halls Reginald 3 238-95-7766 Carson Cheryl 4 722-51-5454 DeFrance Michel 5 712-45-1867 del Castillo Innes 6 427-17-2319 Dull Ann 7 213-46-8915 Green Marjorie I would really apericate the help Regards Naveed Akbar
I don't know what world I'm living in but I have never felt the need to
'number' a recordset in SQL. So I am sticking to my point, no matter what (at my age, I have the right to do this). I don't know MySQL but probably will meet Yukon someday (when is that, 2006???). Besides that, sorry to have shocked you. :-( I have never eaten crow but I would try it. Must taste like chicken. :-) Happy New Year [quoted text, click to view] "Otis B. Driftwood" <nospam@aol.com> wrote in message news:%23AGGi4xzDHA.2224@TK2MSFTNGP09.phx.gbl... > "Raymond D'Anjou (raydan)" <raydan@canatrade.nospamcom> wrote in message > news:OyDpD1wzDHA.3468@TK2MSFTNGP11.phx.gbl... > > Although Tom's solution works (and is verrrry interesting), > > running this on a relatively small table in my database > > takes about 10 times as long as one without the "count" column. > > I'm shocked,shocked! :~) > > > This is best done client side. > > This is why other enterprise databases have sql99 OLAP functions > to do this kind of thing.(You can even do this in MySql without > that hideous ancient subquery:).If Yukon doesn't have them you can still > stick to your point, otherwise prepare to eat some crow:~) > > Happy New Year, > OBD > > > > > >
[quoted text, click to view]
>> ... I have never felt the need to'number' a recordset in SQL. << Likewise. The newbies don't know about tiered architectures or networks and do not have multiple programming language skills because they grew up with a PC or (shudder) a video game view of the world. They honestly think that playing "mousey clicky" is programming -- look at the number of posting here where the guy doesn't know whst DDL is and cannot write. [quoted text, click to view] >> I don't know MySQL but probably will meet Yukon someday (when is that, 2006???). <<MySQL is a file system with a near-SQL dialect on the front. It is handy when your web app needs a file. I am starting to think that waiting for Yukon is much like marrying a woman who promises you how good its going to be .. next year. [quoted text, click to view] >> I have never eaten crow but I would try it. Must taste like chicken. <<Somewhere between Spotted Owl and Whopping Crane, but not as tough as Bald Eagle. --CELKO-- =========================== Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, datatypes, etc. in your schema are. *** Sent via Developersdex http://www.developersdex.com ***
[quoted text, click to view] "Raymond D'Anjou (raydan)" wrote: Remember Casablanca:)> I don't know what world I'm living in but I have never felt the need to > 'number' a recordset in SQL. > So I am sticking to my point, no matter what (at my age, I have the right to > do this). > I don't know MySQL but probably will meet Yukon someday (when is that, > 2006???). > > Besides that, sorry to have shocked you. :-( [quoted text, click to view] > I have never eaten crow but I would try it. Must taste like chicken. :-) Many times a little personality can go a lot further that a lot of good sql. Maybe in your world too :~) HTH :)
[quoted text, click to view]
> > Besides that, sorry to have shocked you. :-( I told you I was old. I think I was there for the premiere. :-(> Remember Casablanca:) [quoted text, click to view] > > I have never eaten crow but I would try it. Must taste like chicken. :-) Of course.> Many times a little personality can go a lot further that a lot of good sql. > Maybe in your world too :~) I have enough to laugh at myself and try to make other people laugh (or at least smile). I do confess that my SQL skills are not up to par with some other posters here. I'm here to learn (yeah, even at my age) and to try to contribute. To get back to the subject, I'm in the group that believes that a database exists to organize, store and return data, not present it. But I cheat and format every once and a while (SELECT CONVERT(CHAR(10), GETDATE(), 111)) ;-)
[quoted text, click to view]
"Joe Celko" <joe.celko@northface.edu> wrote in message news:%23r90LP8zDHA.2328@TK2MSFTNGP10.phx.gbl... > >> ... I have never felt the need to'number' a recordset in SQL. << > > Likewise. The newbies don't know about tiered architectures or networks > and do not have multiple programming language skills because they grew > up with a PC or (shudder) a video game view of the world. They honestly > think that playing "mousey clicky" is programming -- look at the number > of posting here where the guy doesn't know whst DDL is and cannot write. Heh, Joe agrees with me!!! Pinch me. Ouch. Quick, what's the date? I've got to write this down. :-))))
Correct me if I'm wrong, but isn't this the type of subquery you have to
write for the "top three salesman" type problem? (First introduced to me personally when I read one of Celko's books. <s> ). So, putting aside the argument regarding whether or not to number a rowset in the front or the back end, would it be a good idea if databases did have optimizations to handle this concept? Joe Lax [quoted text, click to view] "Joe Celko" <joe.celko@northface.edu> wrote in message news:%23r90LP8zDHA.2328@TK2MSFTNGP10.phx.gbl... > >> ... I have never felt the need to'number' a recordset in SQL. << > > Likewise. The newbies don't know about tiered architectures or networks > and do not have multiple programming language skills because they grew > up with a PC or (shudder) a video game view of the world. They honestly > think that playing "mousey clicky" is programming -- look at the number > of posting here where the guy doesn't know whst DDL is and cannot write. > > >> I don't know MySQL but probably will meet Yukon someday (when is > that, 2006???). << > > MySQL is a file system with a near-SQL dialect on the front. It is > handy when your web app needs a file. > > I am starting to think that waiting for Yukon is much like marrying a > woman who promises you how good its going to be .. next year. > > >> I have never eaten crow but I would try it. Must taste like chicken. > << > > Somewhere between Spotted Owl and Whopping Crane, but not as tough as > Bald Eagle. > > --CELKO-- > =========================== > Please post DDL, so that people do not have to guess what the keys, > constraints, Declarative Referential Integrity, datatypes, etc. in your > schema are. > > *** Sent via Developersdex http://www.developersdex.com *** > Don't just participate in USENET...get rewarded for it!
Don't see what you're looking for? Try a search.
|
January 2003
March 2003
April 2003
May 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
April 2008
August 2008
| home · blog · groups | Questions? Comments? Contact the dn |