|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Returned first row of a column in a group byHi all,
Trying to select rows in a group by and, works great when not using L7.[V]AS [VD] and L7.[H]AS [HD] that is because there are different set of numbers for all rows. I just need the first L7.[V]AS [VD] and L7.[H]AS [HD] number returned from each group? SELECT COUNT(*)AS Total, SA.API_M, SA.S, L7.[V]AS [VD], L7.[H]AS [HD] FROM SW_AR SA INNER JOIN L..[LG 7] L7 ON SA.SW = L7.SW WHERE SA.SW = SA.SW GROUP BY SA.API_M, SA.S, L7.[VD], L7.[HD] thanks gv gv wrote:
Show quote > Hi all, What is the "first" row of the group? Tables have no inherent order.> > Trying to select rows in a group by and, works great > when not using L7.[V]AS [VD] and L7.[H]AS [HD] that is because > there are different set of numbers for all rows. > I just need the first L7.[V]AS [VD] and L7.[H]AS [HD] number returned from > each group? > > SELECT COUNT(*)AS Total, SA.API_M, SA.S, > L7.[V]AS [VD], > L7.[H]AS [HD] > FROM SW_AR SA INNER JOIN > L..[LG 7] L7 ON SA.SW = L7.SW > WHERE SA.SW = SA.SW > GROUP BY SA.API_M, SA.S, L7.[VD], L7.[HD] > > thanks > gv Maybe you should use the MIN() aggregate function. If you need more help, please post CREATE TABLE statement(s) for your table so that we can see what the keys and constraints are. Some sample data would help too. -- David Portas, SQL Server MVP Whenever possible please post enough code to reproduce your problem. Including CREATE TABLE and INSERT statements usually helps. State what version of SQL Server you are using and specify the content of any error messages. SQL Server Books Online: http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx -- Ok, thanks for your help
Here is sample data: The second query should return the first row of the group columns (Vcord and Hcord) if Total count is more then 1 so Min will not work becuse 2 columns are part of a set the Vcord and Hcord. CREATE TABLE Test1 ( Na VARCHAR(20), Smarket VARCHAR(50), STATE VARCHAR(30)) insert into Test1 (Na,Smarket,STATE) Values ('50ABBB','ABILENE', 'TX') insert into Test1 (Na,Smarket,STATE) Values ('50AHqB','ABILENE', 'TX') insert into Test1 (Na,Smarket,STATE) Values ('20ABBB','ALBANY', 'GA') insert into Test1 (Na,Smarket,STATE) Values ('3ABBB','ALBANY', 'NY') insert into Test1 (Na,Smarket,STATE) Values ('zBB','BILLINGS', 'MT') insert into Test1 (Na,Smarket,STATE) Values ('zBc','ASHEVILLE', 'NC') insert into Test1 (Na,Smarket,STATE) Values ('3ABvvB','BILOXI', 'MS') insert into Test1 (Na,Smarket,STATE) Values ('3ABPUUB','BILOXI', 'MS') Create Table Test2 ( Na Varchar(20), Fr Varchar(30), Vcord VARCHAR(10), Hcord VARCHAR (10)) insert into Test2 (Na,Fr,Vcord,Hcord) Values ('50ABBB', 'ax','200', '112') insert into Test2 (Na,Fr,Vcord,Hcord) Values ('50AHqB', 'ax','67','99') insert into Test2 (Na,Fr,Vcord,Hcord) Values ('20ABBB', 'ax','12','44') insert into Test2 (Na,Fr,Vcord,Hcord) Values ('3ABBB', 'ax','33','77') insert into Test2 (Na,Fr,Vcord,Hcord) Values ('zBB', 'ax','23','4877') insert into Test2 (Na,Fr,Vcord,Hcord) Values ('zBc', 'ax','1111','98765') insert into Test2 (Na,Fr,Vcord,Hcord) Values ('3ABvvB', 'ax','555','76765') insert into Test2 (Na,Fr,Vcord,Hcord) Values ('3ABPUUB', 'ax','3899','54333') Select COUNT(*)AS TOTAL, T1.Smarket, T1.STATE from Test1 T1 INNER JOIN Test2 T2 ON T1.Na = T2.NA GROUP BY T1.Smarket, T1.STATE Select COUNT(*)AS TOTAL, T1.Smarket, T1.STATE, '' AS Vcord, '' as Hcord from Test1 T1 INNER JOIN Test2 T2 ON T1.Na = T2.NA GROUP BY T1.Smarket, T1.STATE drop table test1 drop table test2 thanks gv Show quote "David Portas" <REMOVE_BEFORE_REPLYING_dpor***@acm.org> wrote in message news:1156535319.579077.193320@75g2000cwc.googlegroups.com... > gv wrote: >> Hi all, >> >> Trying to select rows in a group by and, works great >> when not using L7.[V]AS [VD] and L7.[H]AS [HD] that is because >> there are different set of numbers for all rows. >> I just need the first L7.[V]AS [VD] and L7.[H]AS [HD] number returned >> from >> each group? >> >> SELECT COUNT(*)AS Total, SA.API_M, SA.S, >> L7.[V]AS [VD], >> L7.[H]AS [HD] >> FROM SW_AR SA INNER JOIN >> L..[LG 7] L7 ON SA.SW = L7.SW >> WHERE SA.SW = SA.SW >> GROUP BY SA.API_M, SA.S, L7.[VD], L7.[HD] >> >> thanks >> gv > > What is the "first" row of the group? Tables have no inherent order. > Maybe you should use the MIN() aggregate function. If you need more > help, please post CREATE TABLE statement(s) for your table so that we > can see what the keys and constraints are. Some sample data would help > too. > > -- > David Portas, SQL Server MVP > > Whenever possible please post enough code to reproduce your problem. > Including CREATE TABLE and INSERT statements usually helps. > State what version of SQL Server you are using and specify the content > of any error messages. > > SQL Server Books Online: > http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx > -- > Here is some more data so If i don't group I get this:
1 ABILENE TX 08614 04459 1 ABILENE TX 08660 04619 1 ABILENE TX 08662 04671 1 ABILENE TX 08676 04624 1 ABILENE TX 08689 04580 I want it look like this: 5ABILENE TX 08614 04459 so this would be the first row in a group 08614 04459 thks gv Show quote "David Portas" <REMOVE_BEFORE_REPLYING_dpor***@acm.org> wrote in message news:1156535319.579077.193320@75g2000cwc.googlegroups.com... > gv wrote: >> Hi all, >> >> Trying to select rows in a group by and, works great >> when not using L7.[V]AS [VD] and L7.[H]AS [HD] that is because >> there are different set of numbers for all rows. >> I just need the first L7.[V]AS [VD] and L7.[H]AS [HD] number returned >> from >> each group? >> >> SELECT COUNT(*)AS Total, SA.API_M, SA.S, >> L7.[V]AS [VD], >> L7.[H]AS [HD] >> FROM SW_AR SA INNER JOIN >> L..[LG 7] L7 ON SA.SW = L7.SW >> WHERE SA.SW = SA.SW >> GROUP BY SA.API_M, SA.S, L7.[VD], L7.[HD] >> >> thanks >> gv > > What is the "first" row of the group? Tables have no inherent order. > Maybe you should use the MIN() aggregate function. If you need more > help, please post CREATE TABLE statement(s) for your table so that we > can see what the keys and constraints are. Some sample data would help > too. > > -- > David Portas, SQL Server MVP > > Whenever possible please post enough code to reproduce your problem. > Including CREATE TABLE and INSERT statements usually helps. > State what version of SQL Server you are using and specify the content > of any error messages. > > SQL Server Books Online: > http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx > -- > On Fri, 25 Aug 2006 16:45:23 -0400, gv wrote:
>Here is some more data so If i don't group I get this: Hi gv,> >1 ABILENE TX 08614 04459 >1 ABILENE TX 08660 04619 >1 ABILENE TX 08662 04671 >1 ABILENE TX 08676 04624 >1 ABILENE TX 08689 04580 > >I want it look like this: >5ABILENE TX 08614 04459 > >so this would be the first row in a group 08614 04459 As David already said, there is no "first" row in a group. But judging by this sample data, you want the minimum values. Try this: SELECT COUNT(*)AS Total, SA.API_M, SA.S, MIN(L7.[V]) AS [VD], MIN(L7.[H]) AS [HD] FROM SW_AR SA INNER JOIN L..[LG 7] AS L7 ON SA.SW = L7.SW WHERE SA.SW = SA.SW GROUP BY SA.API_M, SA.S; -- Hugo Kornelis, SQL Server MVP Ok I'm confused about no first row.
First row (top 1) returned in a result set seen in a grid returned by a select query. Min will not work becuase it will return values from the two different columns. just pretend that set of numbers represents coordinates. 1 ABILENE TX 08614 04459 1 ABILENE TX 08660 04619 1 ABILENE TX 08662 04671 1 ABILENE TX 08676 24 1 ABILENE TX 1 04580 I want it look like this: 5 ABILENE TX 08660 04619 thanks gv Show quote "Hugo Kornelis" <h***@perFact.REMOVETHIS.info.INVALID> wrote in message news:httue25ah0qoep4ovke93lg0fg4e9jgbju@4ax.com... > On Fri, 25 Aug 2006 16:45:23 -0400, gv wrote: > >>Here is some more data so If i don't group I get this: >> >>1 ABILENE TX 08614 04459 >>1 ABILENE TX 08660 04619 >>1 ABILENE TX 08662 04671 >>1 ABILENE TX 08676 04624 >>1 ABILENE TX 08689 04580 >> >>I want it look like this: >>5ABILENE TX 08614 04459 >> >>so this would be the first row in a group 08614 04459 > > Hi gv, > > As David already said, there is no "first" row in a group. But judging > by this sample data, you want the minimum values. Try this: > > SELECT COUNT(*)AS Total, SA.API_M, SA.S, > MIN(L7.[V]) AS [VD], MIN(L7.[H]) AS [HD] > FROM SW_AR SA > INNER JOIN L..[LG 7] AS L7 > ON SA.SW = L7.SW > WHERE SA.SW = SA.SW > GROUP BY SA.API_M, SA.S; > > -- > Hugo Kornelis, SQL Server MVP The questions STILL come up:
How do we know which is the 'first' row? What do you mean by 'first' row? I don't see how you are making the decision to return 08660/04619 over the other options... It's not the lowest [V] or [H], highest [V] or [H], lowest ([V],[H]), highest ([V],[H]). It's not the lowest or highest absolute value of the difference between [V] and [H]. (Maybe it's just me, but I just don't get it.) It could have been the first row in inserted in the table -but there is no way from the values displayed to 'intuit' that. Helps us here so we can help you. -- Show quoteArnie Rowland, Ph.D. Westwood Consulting, Inc Most good judgment comes from experience. Most experience comes from bad judgment. - Anonymous "Gerry Viator" <viator.ge***@gmail.com> wrote in message news:%235Te3PLyGHA.4392@TK2MSFTNGP04.phx.gbl... > Ok I'm confused about no first row. > > First row (top 1) returned in a result set seen in a grid returned by a > select query. > Min will not work becuase it will return values from the two different > columns. > just pretend that set of numbers represents coordinates. > > 1 ABILENE TX 08614 04459 > 1 ABILENE TX 08660 04619 > 1 ABILENE TX 08662 04671 > 1 ABILENE TX 08676 24 > 1 ABILENE TX 1 04580 > > I want it look like this: > > 5 ABILENE TX 08660 04619 > > thanks > gv > > "Hugo Kornelis" <h***@perFact.REMOVETHIS.info.INVALID> wrote in message > news:httue25ah0qoep4ovke93lg0fg4e9jgbju@4ax.com... >> On Fri, 25 Aug 2006 16:45:23 -0400, gv wrote: >> >>>Here is some more data so If i don't group I get this: >>> >>>1 ABILENE TX 08614 04459 >>>1 ABILENE TX 08660 04619 >>>1 ABILENE TX 08662 04671 >>>1 ABILENE TX 08676 04624 >>>1 ABILENE TX 08689 04580 >>> >>>I want it look like this: >>>5ABILENE TX 08614 04459 >>> >>>so this would be the first row in a group 08614 04459 >> >> Hi gv, >> >> As David already said, there is no "first" row in a group. But judging >> by this sample data, you want the minimum values. Try this: >> >> SELECT COUNT(*)AS Total, SA.API_M, SA.S, >> MIN(L7.[V]) AS [VD], MIN(L7.[H]) AS [HD] >> FROM SW_AR SA >> INNER JOIN L..[LG 7] AS L7 >> ON SA.SW = L7.SW >> WHERE SA.SW = SA.SW >> GROUP BY SA.API_M, SA.S; >> >> -- >> Hugo Kornelis, SQL Server MVP > > hi all ,
i just went thru the discussion i hv the same query i m using "groupby" on 2 fields remaining 3 fields hv different values and i want to pick ANY OF one row. CREATE TABLE [dbo].[TableData] ( [SNO] [decimal](18, 0) IDENTITY (1, 1) NOT NULL , [CLI] [nvarchar] (50), [Programid] [int] , [callername] [nvarchar] (50) , [calleraddress] [nvarchar] (200) , ) ON [PRIMARY] insert into TableData Values ('23233331',4, 'sadia','bhbhbhbhbhb') insert into TableData Values ('23233331',4, 'dia','yuyuyuyuyu') insert into TableData Values ('23233331',4, 'tabassum','dsdsdsdsdsds') insert into TableData Values ('23233332',4, 'dsdsds','zxzxzxzxzxzxz') insert into TableData Values ('23233332',4, 'sasasa','jkjkjkjkjkjkjkjkjkj') insert into TableData Values ('23233332',4, 'dearr','uiuiuiuiuiuiuiuiu') insert into TableData Values ('23233332',4, 'jannnn','nmnmkkkmkmkm') select CLI,Programid,Callername,Calleraddress from TableData group by Programid,CLI i know the query is wrong it gives the error msg that Callername and CallerAddress are invalid in select statement because they are not contained in group by clause i want that while grouping it pick any of callerrname and calleraddress combination i thnk gv has the same problem is there ne solution hv a nice day sadia Arnie Rowland wrote: Show quote > The questions STILL come up: > > How do we know which is the 'first' row? > What do you mean by 'first' row? > > I don't see how you are making the decision to return 08660/04619 over the > other options... > > It's not the lowest [V] or [H], highest [V] or [H], lowest ([V],[H]), > highest ([V],[H]). It's not the lowest or highest absolute value of the > difference between [V] and [H]. (Maybe it's just me, but I just don't get > it.) > > It could have been the first row in inserted in the table -but there is no > way from the values displayed to 'intuit' that. > > Helps us here so we can help you. > > -- > Arnie Rowland, Ph.D. > Westwood Consulting, Inc > > Most good judgment comes from experience. > Most experience comes from bad judgment. > - Anonymous > > > "Gerry Viator" <viator.ge***@gmail.com> wrote in message > news:%235Te3PLyGHA.4392@TK2MSFTNGP04.phx.gbl... > > Ok I'm confused about no first row. > > > > First row (top 1) returned in a result set seen in a grid returned by a > > select query. > > Min will not work becuase it will return values from the two different > > columns. > > just pretend that set of numbers represents coordinates. > > > > 1 ABILENE TX 08614 04459 > > 1 ABILENE TX 08660 04619 > > 1 ABILENE TX 08662 04671 > > 1 ABILENE TX 08676 24 > > 1 ABILENE TX 1 04580 > > > > I want it look like this: > > > > 5 ABILENE TX 08660 04619 > > > > thanks > > gv > > > > "Hugo Kornelis" <h***@perFact.REMOVETHIS.info.INVALID> wrote in message > > news:httue25ah0qoep4ovke93lg0fg4e9jgbju@4ax.com... > >> On Fri, 25 Aug 2006 16:45:23 -0400, gv wrote: > >> > >>>Here is some more data so If i don't group I get this: > >>> > >>>1 ABILENE TX 08614 04459 > >>>1 ABILENE TX 08660 04619 > >>>1 ABILENE TX 08662 04671 > >>>1 ABILENE TX 08676 04624 > >>>1 ABILENE TX 08689 04580 > >>> > >>>I want it look like this: > >>>5ABILENE TX 08614 04459 > >>> > >>>so this would be the first row in a group 08614 04459 > >> > >> Hi gv, > >> > >> As David already said, there is no "first" row in a group. But judging > >> by this sample data, you want the minimum values. Try this: > >> > >> SELECT COUNT(*)AS Total, SA.API_M, SA.S, > >> MIN(L7.[V]) AS [VD], MIN(L7.[H]) AS [HD] > >> FROM SW_AR SA > >> INNER JOIN L..[LG 7] AS L7 > >> ON SA.SW = L7.SW > >> WHERE SA.SW = SA.SW > >> GROUP BY SA.API_M, SA.S; > >> > >> -- > >> Hugo Kornelis, SQL Server MVP > > > > Here's a query that will return the callername and calleraddress for one of
the matching rows (actually, it will use the one with the smallest value for SNO). The query is only valid if there are no duplicate values for SNO, such as, for example, SNO is the primary key for your table. select x.CLI, x.Programid, t.callername, t.calleraddress from (select CLI,Programid, min(SNO) as SNO from TableData group by Programid,CLI) x inner join TableData t on x.SNO = t.SNO order by x.Programid,x.CLI Tom <s4sa***@gmail.com> wrote in message Show quote news:1156571137.916003.55180@m73g2000cwd.googlegroups.com... > hi all , > i just went thru the discussion > > i hv the same query > i m using "groupby" on 2 fields remaining 3 fields hv different values > and i want to pick ANY OF one row. > > CREATE TABLE [dbo].[TableData] > ( > [SNO] [decimal](18, 0) IDENTITY (1, 1) NOT NULL , > [CLI] [nvarchar] (50), > [Programid] [int] , > [callername] [nvarchar] (50) , > [calleraddress] [nvarchar] (200) , > ) ON [PRIMARY] > > insert into TableData Values ('23233331',4, 'sadia','bhbhbhbhbhb') > insert into TableData Values ('23233331',4, 'dia','yuyuyuyuyu') > insert into TableData Values ('23233331',4, 'tabassum','dsdsdsdsdsds') > insert into TableData Values ('23233332',4, 'dsdsds','zxzxzxzxzxzxz') > insert into TableData Values ('23233332',4, > 'sasasa','jkjkjkjkjkjkjkjkjkj') > insert into TableData Values ('23233332',4, > 'dearr','uiuiuiuiuiuiuiuiu') > insert into TableData Values ('23233332',4, 'jannnn','nmnmkkkmkmkm') > > > select CLI,Programid,Callername,Calleraddress from TableData group by > Programid,CLI > > i know the query is wrong it gives the error msg that Callername and > CallerAddress are invalid in select statement because they are not > contained in group by clause > > i want that while grouping it pick any of callerrname and calleraddress > combination > > i thnk gv has the same problem > > is there ne solution > > hv a nice day > sadia > > > > Arnie Rowland wrote: >> The questions STILL come up: >> >> How do we know which is the 'first' row? >> What do you mean by 'first' row? >> >> I don't see how you are making the decision to return 08660/04619 over >> the >> other options... >> >> It's not the lowest [V] or [H], highest [V] or [H], lowest ([V],[H]), >> highest ([V],[H]). It's not the lowest or highest absolute value of the >> difference between [V] and [H]. (Maybe it's just me, but I just don't get >> it.) >> >> It could have been the first row in inserted in the table -but there is >> no >> way from the values displayed to 'intuit' that. >> >> Helps us here so we can help you. >> >> -- >> Arnie Rowland, Ph.D. >> Westwood Consulting, Inc >> >> Most good judgment comes from experience. >> Most experience comes from bad judgment. >> - Anonymous >> >> >> "Gerry Viator" <viator.ge***@gmail.com> wrote in message >> news:%235Te3PLyGHA.4392@TK2MSFTNGP04.phx.gbl... >> > Ok I'm confused about no first row. >> > >> > First row (top 1) returned in a result set seen in a grid returned by a >> > select query. >> > Min will not work becuase it will return values from the two different >> > columns. >> > just pretend that set of numbers represents coordinates. >> > >> > 1 ABILENE TX 08614 04459 >> > 1 ABILENE TX 08660 04619 >> > 1 ABILENE TX 08662 04671 >> > 1 ABILENE TX 08676 24 >> > 1 ABILENE TX 1 04580 >> > >> > I want it look like this: >> > >> > 5 ABILENE TX 08660 04619 >> > >> > thanks >> > gv >> > >> > "Hugo Kornelis" <h***@perFact.REMOVETHIS.info.INVALID> wrote in message >> > news:httue25ah0qoep4ovke93lg0fg4e9jgbju@4ax.com... >> >> On Fri, 25 Aug 2006 16:45:23 -0400, gv wrote: >> >> >> >>>Here is some more data so If i don't group I get this: >> >>> >> >>>1 ABILENE TX 08614 04459 >> >>>1 ABILENE TX 08660 04619 >> >>>1 ABILENE TX 08662 04671 >> >>>1 ABILENE TX 08676 04624 >> >>>1 ABILENE TX 08689 04580 >> >>> >> >>>I want it look like this: >> >>>5ABILENE TX 08614 04459 >> >>> >> >>>so this would be the first row in a group 08614 04459 >> >> >> >> Hi gv, >> >> >> >> As David already said, there is no "first" row in a group. But judging >> >> by this sample data, you want the minimum values. Try this: >> >> >> >> SELECT COUNT(*)AS Total, SA.API_M, SA.S, >> >> MIN(L7.[V]) AS [VD], MIN(L7.[H]) AS [HD] >> >> FROM SW_AR SA >> >> INNER JOIN L..[LG 7] AS L7 >> >> ON SA.SW = L7.SW >> >> WHERE SA.SW = SA.SW >> >> GROUP BY SA.API_M, SA.S; >> >> >> >> -- >> >> Hugo Kornelis, SQL Server MVP >> > >> > > I should have added the following to my answer.
You asked if this was the same as the problem that gv had. It's very close, but there was an important difference. You just wanted the values from any one of the rows which had the same values in the group by columns. So I just picked the one with the smallest SNO. gv asked for the "first" row in each group, but then did not give a definition of what the "first" row was. So there was no way for any of us to give him an answer. Tom Show quote "Tom Cooper" <tomcooper@comcast.no.spam.please.net> wrote in message news:-amdnVpiI7LlbHLZnZ2dnUVZ_vqdnZ2d@comcast.com... > Here's a query that will return the callername and calleraddress for one > of the matching rows (actually, it will use the one with the smallest > value for SNO). The query is only valid if there are no duplicate values > for SNO, such as, for example, SNO is the primary key for your table. > > select x.CLI, x.Programid, t.callername, t.calleraddress > from (select CLI,Programid, min(SNO) as SNO > from TableData > group by Programid,CLI) x > inner join TableData t on x.SNO = t.SNO > order by x.Programid,x.CLI > > Tom > > <s4sa***@gmail.com> wrote in message > news:1156571137.916003.55180@m73g2000cwd.googlegroups.com... >> hi all , >> i just went thru the discussion >> >> i hv the same query >> i m using "groupby" on 2 fields remaining 3 fields hv different values >> and i want to pick ANY OF one row. >> >> CREATE TABLE [dbo].[TableData] >> ( >> [SNO] [decimal](18, 0) IDENTITY (1, 1) NOT NULL , >> [CLI] [nvarchar] (50), >> [Programid] [int] , >> [callername] [nvarchar] (50) , >> [calleraddress] [nvarchar] (200) , >> ) ON [PRIMARY] >> >> insert into TableData Values ('23233331',4, 'sadia','bhbhbhbhbhb') >> insert into TableData Values ('23233331',4, 'dia','yuyuyuyuyu') >> insert into TableData Values ('23233331',4, 'tabassum','dsdsdsdsdsds') >> insert into TableData Values ('23233332',4, 'dsdsds','zxzxzxzxzxzxz') >> insert into TableData Values ('23233332',4, >> 'sasasa','jkjkjkjkjkjkjkjkjkj') >> insert into TableData Values ('23233332',4, >> 'dearr','uiuiuiuiuiuiuiuiu') >> insert into TableData Values ('23233332',4, 'jannnn','nmnmkkkmkmkm') >> >> >> select CLI,Programid,Callername,Calleraddress from TableData group by >> Programid,CLI >> >> i know the query is wrong it gives the error msg that Callername and >> CallerAddress are invalid in select statement because they are not >> contained in group by clause >> >> i want that while grouping it pick any of callerrname and calleraddress >> combination >> >> i thnk gv has the same problem >> >> is there ne solution >> >> hv a nice day >> sadia >> >> >> >> Arnie Rowland wrote: >>> The questions STILL come up: >>> >>> How do we know which is the 'first' row? >>> What do you mean by 'first' row? >>> >>> I don't see how you are making the decision to return 08660/04619 over >>> the >>> other options... >>> >>> It's not the lowest [V] or [H], highest [V] or [H], lowest ([V],[H]), >>> highest ([V],[H]). It's not the lowest or highest absolute value of the >>> difference between [V] and [H]. (Maybe it's just me, but I just don't >>> get >>> it.) >>> >>> It could have been the first row in inserted in the table -but there is >>> no >>> way from the values displayed to 'intuit' that. >>> >>> Helps us here so we can help you. >>> >>> -- >>> Arnie Rowland, Ph.D. >>> Westwood Consulting, Inc >>> >>> Most good judgment comes from experience. >>> Most experience comes from bad judgment. >>> - Anonymous >>> >>> >>> "Gerry Viator" <viator.ge***@gmail.com> wrote in message >>> news:%235Te3PLyGHA.4392@TK2MSFTNGP04.phx.gbl... >>> > Ok I'm confused about no first row. >>> > >>> > First row (top 1) returned in a result set seen in a grid returned by >>> > a >>> > select query. >>> > Min will not work becuase it will return values from the two different >>> > columns. >>> > just pretend that set of numbers represents coordinates. >>> > >>> > 1 ABILENE TX 08614 04459 >>> > 1 ABILENE TX 08660 04619 >>> > 1 ABILENE TX 08662 04671 >>> > 1 ABILENE TX 08676 24 >>> > 1 ABILENE TX 1 04580 >>> > >>> > I want it look like this: >>> > >>> > 5 ABILENE TX 08660 04619 >>> > >>> > thanks >>> > gv >>> > >>> > "Hugo Kornelis" <h***@perFact.REMOVETHIS.info.INVALID> wrote in >>> > message >>> > news:httue25ah0qoep4ovke93lg0fg4e9jgbju@4ax.com... >>> >> On Fri, 25 Aug 2006 16:45:23 -0400, gv wrote: >>> >> >>> >>>Here is some more data so If i don't group I get this: >>> >>> >>> >>>1 ABILENE TX 08614 04459 >>> >>>1 ABILENE TX 08660 04619 >>> >>>1 ABILENE TX 08662 04671 >>> >>>1 ABILENE TX 08676 04624 >>> >>>1 ABILENE TX 08689 04580 >>> >>> >>> >>>I want it look like this: >>> >>>5ABILENE TX 08614 04459 >>> >>> >>> >>>so this would be the first row in a group 08614 04459 >>> >> >>> >> Hi gv, >>> >> >>> >> As David already said, there is no "first" row in a group. But >>> >> judging >>> >> by this sample data, you want the minimum values. Try this: >>> >> >>> >> SELECT COUNT(*)AS Total, SA.API_M, SA.S, >>> >> MIN(L7.[V]) AS [VD], MIN(L7.[H]) AS [HD] >>> >> FROM SW_AR SA >>> >> INNER JOIN L..[LG 7] AS L7 >>> >> ON SA.SW = L7.SW >>> >> WHERE SA.SW = SA.SW >>> >> GROUP BY SA.API_M, SA.S; >>> >> >>> >> -- >>> >> Hugo Kornelis, SQL Server MVP >>> > >>> > >> > > On Fri, 25 Aug 2006 22:04:30 -0400, Gerry Viator wrote:
>Ok I'm confused about no first row. Hi Gerry,That's as defined by relational theory. A table in a relational database is BY DEFINITION an *un*ordered set of rows. The only wayy to pinpoint a specific rows is by means of it's data. I.e., you can pinpoint the row with Person_ID 12235 and Valid_Date 19950218, but yoou can't pinpoint a row by saying "the third row from the top". >First row (top 1) returned in a result set seen in a grid returned by a I'm still cursing Microsoft for allowing the TOP prrefix in a query>select query. wiithout an ORDER BY clause. The results of such a query are undefined. You will get the first row by whatever execution plan the optimizer sees fit to use - which can change from execution to execution. >Min will not work becuase it will return values from the two different Ah, too bad. I was going by the example data supplied, but I was afraid>columns. that there's the possibility that the real data is more complicated. Time to go for the more complex queries, then! >just pretend that set of numbers represents coordinates. Great. I can do that. But only if I understand why you wanted _that> >1 ABILENE TX 08614 04459 >1 ABILENE TX 08660 04619 >1 ABILENE TX 08662 04671 >1 ABILENE TX 08676 24 >1 ABILENE TX 1 04580 > >I want it look like this: > >5 ABILENE TX 08660 04619 specific row_ to be returned. Why not any of the others? And please don't say "because it's second in the list", as this order is lost as soon as you insert the data in a relational table. And also, don't say "oh, I don't mind which", since SQL Server doesn't have an "ANY_I_DONT_CARE_WHICH" operator. Anyway, suppose that you'd want to have the row with the lowest V value (which is probably incorrect, since it's not coonsistant with the example above, and if you can't be sure that there's only one row with the lowest V value, then this criterium is not enough - but maybe this is a good starting point), then use something like the below. It can probably be simplified, buut I'd need to know more about yoour tables for that (see www.aspfaq.com/5006). SELECT A.Total, A.API_M, A.S, A.VD, B.H AS HD FROM (SELECT COUNT(*)AS Total, SA.API_M, SA.S, MIN(L7.V) AS VD FROM SW_AR AS SA INNER JOIN L..[LG 7] AS L7 ON SA.SW = L7.SW GROUP BY SA.API_M, SA.S) AS A INNER JOIN (SELECT SA.API_M, SA.S, L7.V, L7.H FROM SW_AR AS SA INNER JOIN L..[LG 7] AS L7 ON SA.SW = L7.SW) AS B ON B.API_M = A.API_M AND B.S = A.S AND B.V = A.VD; (Untested - see www.aspfaq.com/5006 if you prefer a tested reply) -- Hugo Kornelis, SQL Server MVP I probably missed saying one thing and sorry for not suppling
enough information It doesn't matter which row is being returned, I just said first row on making it easy. It can be any row from the Select group but the two cordinates need to stay together. thanks gv Show quote "Hugo Kornelis" <h***@perFact.REMOVETHIS.info.INVALID> wrote in message news:q654f2p3pfk35hbqggqglsjmuhmrcbrh8u@4ax.com... > On Fri, 25 Aug 2006 22:04:30 -0400, Gerry Viator wrote: > >>Ok I'm confused about no first row. > > Hi Gerry, > > That's as defined by relational theory. A table in a relational database > is BY DEFINITION an *un*ordered set of rows. The only wayy to pinpoint a > specific rows is by means of it's data. I.e., you can pinpoint the row > with Person_ID 12235 and Valid_Date 19950218, but yoou can't pinpoint a > row by saying "the third row from the top". > >>First row (top 1) returned in a result set seen in a grid returned by a >>select query. > > I'm still cursing Microsoft for allowing the TOP prrefix in a query > wiithout an ORDER BY clause. The results of such a query are undefined. > You will get the first row by whatever execution plan the optimizer sees > fit to use - which can change from execution to execution. > >>Min will not work becuase it will return values from the two different >>columns. > > Ah, too bad. I was going by the example data supplied, but I was afraid > that there's the possibility that the real data is more complicated. > > Time to go for the more complex queries, then! > >>just pretend that set of numbers represents coordinates. >> >>1 ABILENE TX 08614 04459 >>1 ABILENE TX 08660 04619 >>1 ABILENE TX 08662 04671 >>1 ABILENE TX 08676 24 >>1 ABILENE TX 1 04580 >> >>I want it look like this: >> >>5 ABILENE TX 08660 04619 > > Great. I can do that. But only if I understand why you wanted _that > specific row_ to be returned. Why not any of the others? And please > don't say "because it's second in the list", as this order is lost as > soon as you insert the data in a relational table. And also, don't say > "oh, I don't mind which", since SQL Server doesn't have an > "ANY_I_DONT_CARE_WHICH" operator. > > Anyway, suppose that you'd want to have the row with the lowest V value > (which is probably incorrect, since it's not coonsistant with the > example above, and if you can't be sure that there's only one row with > the lowest V value, then this criterium is not enough - but maybe this > is a good starting point), then use something like the below. It can > probably be simplified, buut I'd need to know more about yoour tables > for that (see www.aspfaq.com/5006). > > SELECT A.Total, A.API_M, A.S, A.VD, B.H AS HD > FROM (SELECT COUNT(*)AS Total, SA.API_M, SA.S, > MIN(L7.V) AS VD > FROM SW_AR AS SA > INNER JOIN L..[LG 7] AS L7 > ON SA.SW = L7.SW > GROUP BY SA.API_M, SA.S) AS A > INNER JOIN (SELECT SA.API_M, SA.S, L7.V, L7.H > FROM SW_AR AS SA > INNER JOIN L..[LG 7] AS L7 > ON SA.SW = L7.SW) AS B > ON B.API_M = A.API_M > AND B.S = A.S > AND B.V = A.VD; > > (Untested - see www.aspfaq.com/5006 if you prefer a tested reply) > > -- > Hugo Kornelis, SQL Server MVP Thanks Hugo,
Your query helped me!! Thanks! That is what I was looking for..... I guess MS using TOP told me there were rows, my bad for assuming. thanks for your help gv Show quote "Hugo Kornelis" <h***@perFact.REMOVETHIS.info.INVALID> wrote in message news:q654f2p3pfk35hbqggqglsjmuhmrcbrh8u@4ax.com... > On Fri, 25 Aug 2006 22:04:30 -0400, Gerry Viator wrote: > >>Ok I'm confused about no first row. > > Hi Gerry, > > That's as defined by relational theory. A table in a relational database > is BY DEFINITION an *un*ordered set of rows. The only wayy to pinpoint a > specific rows is by means of it's data. I.e., you can pinpoint the row > with Person_ID 12235 and Valid_Date 19950218, but yoou can't pinpoint a > row by saying "the third row from the top". > >>First row (top 1) returned in a result set seen in a grid returned by a >>select query. > > I'm still cursing Microsoft for allowing the TOP prrefix in a query > wiithout an ORDER BY clause. The results of such a query are undefined. > You will get the first row by whatever execution plan the optimizer sees > fit to use - which can change from execution to execution. > >>Min will not work becuase it will return values from the two different >>columns. > > Ah, too bad. I was going by the example data supplied, but I was afraid > that there's the possibility that the real data is more complicated. > > Time to go for the more complex queries, then! > >>just pretend that set of numbers represents coordinates. >> >>1 ABILENE TX 08614 04459 >>1 ABILENE TX 08660 04619 >>1 ABILENE TX 08662 04671 >>1 ABILENE TX 08676 24 >>1 ABILENE TX 1 04580 >> >>I want it look like this: >> >>5 ABILENE TX 08660 04619 > > Great. I can do that. But only if I understand why you wanted _that > specific row_ to be returned. Why not any of the others? And please > don't say "because it's second in the list", as this order is lost as > soon as you insert the data in a relational table. And also, don't say > "oh, I don't mind which", since SQL Server doesn't have an > "ANY_I_DONT_CARE_WHICH" operator. > > Anyway, suppose that you'd want to have the row with the lowest V value > (which is probably incorrect, since it's not coonsistant with the > example above, and if you can't be sure that there's only one row with > the lowest V value, then this criterium is not enough - but maybe this > is a good starting point), then use something like the below. It can > probably be simplified, buut I'd need to know more about yoour tables > for that (see www.aspfaq.com/5006). > > SELECT A.Total, A.API_M, A.S, A.VD, B.H AS HD > FROM (SELECT COUNT(*)AS Total, SA.API_M, SA.S, > MIN(L7.V) AS VD > FROM SW_AR AS SA > INNER JOIN L..[LG 7] AS L7 > ON SA.SW = L7.SW > GROUP BY SA.API_M, SA.S) AS A > INNER JOIN (SELECT SA.API_M, SA.S, L7.V, L7.H > FROM SW_AR AS SA > INNER JOIN L..[LG 7] AS L7 > ON SA.SW = L7.SW) AS B > ON B.API_M = A.API_M > AND B.S = A.S > AND B.V = A.VD; > > (Untested - see www.aspfaq.com/5006 if you prefer a tested reply) > > -- > Hugo Kornelis, SQL Server MVP First row that was put in the table, last row?
Would it work to wrap l7.V/l7.H with min(), max(), avg()? A little more information would be helpful... -- Show quoteArnie Rowland, Ph.D. Westwood Consulting, Inc Most good judgment comes from experience. Most experience comes from bad judgment. - Anonymous "gv" <viator.ge***@gmail.com> wrote in message news:O22aR5HyGHA.4392@TK2MSFTNGP04.phx.gbl... > Hi all, > > Trying to select rows in a group by and, works great > when not using L7.[V]AS [VD] and L7.[H]AS [HD] that is because > there are different set of numbers for all rows. > I just need the first L7.[V]AS [VD] and L7.[H]AS [HD] number returned from > each group? > > SELECT COUNT(*)AS Total, SA.API_M, SA.S, > L7.[V]AS [VD], > L7.[H]AS [HD] > FROM SW_AR SA INNER JOIN > L..[LG 7] L7 ON SA.SW = L7.SW > WHERE SA.SW = SA.SW > GROUP BY SA.API_M, SA.S, L7.[VD], L7.[HD] > > thanks > gv >  |
|||||||||||||||||||||||
Other interesting topics