First n records of x groups
in [Programming SQL]
|
Prev: marvendas@gmail.com Kit completo de Solenoides ( solenoid ) + chicotePara Cambio automatico 01M hidramatico Audi A3 Vw Golf gti turbo 36832
Next: output to html
From: Luciano (DOC) on 20 Feb 2010 08:04 Imagine a table containing car models characteristics. I want to select the first n records for each car brand. Can somebody help me? Thanks. Luciano
From: Keith G Hicks on 20 Feb 2010 08:56 Look at my thread "which query is faster, better to use?" from 2010-02-18. Is that the idea you're looking for? If so, there are several solutions as you can see. But instead of TOP 1, you would use TOP 5 or whatever you have in mind. "Luciano (DOC)" <lucianodoc(a)luciano.doc> wrote in message news:4b7fde0d$0$1110$4fafbaef(a)reader2.news.tin.it... > Imagine a table containing car models characteristics. I want to select > the first n records for each car brand. > Can somebody help me? > Thanks. > > Luciano > >
From: --CELKO-- on 20 Feb 2010 09:39 The usual terms are make and model for automobiles. Tables have no ordering and rows are not records, so the concept of "first n records" makes no sense in RDBMS. You did to decide on what ordering you wish to use. Get a book on Basic RDBMS and read the part about the Information Principle. SELECT X.* FROM (SELECT auto_make, auto_model, ROW_NUMBER() OVER (PARTITION BY auto_make ORDER BY ????) AS vague_ordering FROM Motorpool) AS X WHERE X.vague_ordering <= @n;
From: Keith G Hicks on 20 Feb 2010 10:31 Why are you assuming he means make and model? He said models and characteristics. But since you know everything about everything.... LOL. "--CELKO--" <jcelko212(a)earthlink.net> wrote in message news:cd5371e1-8e0b-4771-b20f-055521572ef0(a)k19g2000yqc.googlegroups.com... > The usual terms are make and model for automobiles. Tables have no > ordering and rows are not records, so the concept of "first n records" > makes no sense in RDBMS. You did to decide on what ordering you wish > to use. Get a book on Basic RDBMS and read the part about the > Information Principle. > > SELECT X.* > FROM (SELECT auto_make, auto_model, > ROW_NUMBER() > OVER (PARTITION BY auto_make > ORDER BY ????) AS vague_ordering > FROM Motorpool) AS X > WHERE X.vague_ordering <= @n;
From: Luciano (DOC) on 20 Feb 2010 11:01
"--CELKO--" <jcelko212(a)earthlink.net> ha scritto nel messaggio news:cd5371e1-8e0b-4771-b20f-055521572ef0(a)k19g2000yqc.googlegroups.com... > The usual terms are make and model for automobiles. Tables have no > ordering and rows are not records, so the concept of "first n records" > makes no sense in RDBMS. I didn't specify the order because I might wish different orders: model's year ascending or descending, for example, or name. > SELECT X.* > FROM (SELECT auto_make, auto_model, > ROW_NUMBER() > OVER (PARTITION BY auto_make > ORDER BY ????) AS vague_ordering > FROM Motorpool) AS X > WHERE X.vague_ordering <= @n; Thanks, it works exactly the way I need. I didn't know "OVER PARTITION BY". Bye. Luciano |