|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Need help with sql-query using group byI have a table which look like this: Country | Ordinal | Value US 1 sjja US 2 ksjaj SE 1 pll SE 2 öäläö NO 2 poop NO 3 plål I want the result of the query to be like this: Country | Ordinal | Value US 1 sjja SE 1 pll NO 2 poop Also get the row for each country which has the lowest ordinal. I have tried something like this: select country, min(ordinal) from table group by pricelistcountry .... but I also want to get the column Value from the row. If I add the column Value to the select I also have to add it to the group by clause but then I get all rows in result. Can someone please show me how to accomplich this. Thanks In Advance / Steve SteveSu (ssunnerb***@hotmail.com) writes:
Show quote > I have a table which look like this: SELECT a.Country, a.Ordinal, a.Value> > Country | Ordinal | Value > US 1 sjja > US 2 ksjaj > SE 1 pll > SE 2 öäläö > NO 2 poop > NO 3 plål > > I want the result of the query to be like this: > > Country | Ordinal | Value > US 1 sjja > SE 1 pll > NO 2 poop > FROM tbl a JOIN (SELECT Country, Ordinal = MIN(a.Ordinal) FROM tbl GROUP BY Country) AS b ON a.Country = b.Country AND a.Ordinal = b.Ordinal The thing in parentheses is a *derived* table. You can think of it as a temp table within the query, but it necessarily not materialised. In fact, the optimizer is very good in recasting the evaluation order for good performance. A derived table is an independent query in itself; it does not refer to any tables elsewhere in the query. Derived tables are extremely powerful when you work with complex queries. -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp Hi,
Try this.. Select Country,Ordinal,Value from Table T1 Where Ordinal=(Select Min(Ordinal) from Table T2 where T1.Country=T2.Country) Hope this will help -- Show quoteHerbert "SteveSu" wrote: > Hi! > I have a table which look like this: > > Country | Ordinal | Value > US 1 sjja > US 2 ksjaj > SE 1 pll > SE 2 öäläö > NO 2 poop > NO 3 plål > > I want the result of the query to be like this: > > Country | Ordinal | Value > US 1 sjja > SE 1 pll > NO 2 poop > > Also get the row for each country which has the lowest ordinal. > > I have tried something like this: > select country, min(ordinal) from table > group by pricelistcountry > > .... but I also want to get the column Value from the row. If I add the > column Value to the select I also have to add it to the group by clause > but then I get all rows in result. Can someone please show me how to > accomplich this. > > Thanks In Advance > / Steve > > hi,
try this use northwind select p.supplierid, p.unitprice,p.productid from products p join ( select supplierid, min(unitprice)as unitprice from products group by supplierid ) as p2 on p.supplierid=p2.supplierid and p.unitprice=p2.unitprice -- Show quotethanks, ------------------------------------ Jose de Jesus Jr. Mcp,Mcdba Data Architect Sykes Asia (Manila philippines) MCP #2324787 "SteveSu" wrote: > Hi! > I have a table which look like this: > > Country | Ordinal | Value > US 1 sjja > US 2 ksjaj > SE 1 pll > SE 2 öäläö > NO 2 poop > NO 3 plål > > I want the result of the query to be like this: > > Country | Ordinal | Value > US 1 sjja > SE 1 pll > NO 2 poop > > Also get the row for each country which has the lowest ordinal. > > I have tried something like this: > select country, min(ordinal) from table > group by pricelistcountry > > .... but I also want to get the column Value from the row. If I add the > column Value to the select I also have to add it to the group by clause > but then I get all rows in result. Can someone please show me how to > accomplich this. > > Thanks In Advance > / Steve > > |
|||||||||||||||||||||||
Other interesting topics