How about something like this:
create table test
(
Grp text,
Val text,
Pri integer
)
insert into test values ('A', 'X', 1)
insert into test values ('A', 'X', 2)
insert into test values ('B', 'Y', 4)
insert into test values ('B', 'Z', 2)
insert into test values ('B', 'X', 8)
insert into test values ('C', 'Y', 6)
insert into test values ('C', 'Z', 8)
insert into test values ('C', 'X', 9)
insert into test values ('C', 'Y', 11)
select distinct pri.* from
(
select Grp, min(Pri) as 'Pri'
from test
group by [Grp]
) grp
join
(
select Grp, Val, Pri
from test
) pri
on grp.Grp=pri.Grp and grp.Pri=pri.Pri
Grp Val Pri
---- ---- -----------
A X 1
B Z 2
C Y 6
----- Original Message ----
From: "Adler, Eliedaat" <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Monday, May 7, 2007 8:38:22 AM
Subject: [sqlite] Help wiith SQL - first row of each group
Hi guys,
I have a complex query result set RESULT_TABLE that returns:
G V P
___________
A X 1
A X 2
B Y 4
B Z 2
B X 8
C Y 6
C Z 8
C X 9
C Y 11
G - defines groups
V - some value for that specific row
C - defined a display priority - i.e.
I need to define a query that returns only the "first' row in each group
- i.e. the row with the lowest display priority:
G V P
___________
A X 1
B Z 2
C Y 6
- most preferably a query that doesn't require selecting RESULT_TABLE
more than once.
Thanks,
Eli
***********************************************************************************
This email message and any attachments thereto are intended only for use by the
addressee(s) named above, and may contain legally privileged and/or
confidential information. If the reader of this message is not the intended
recipient, or the employee or agent responsible to deliver it to the intended
recipient, you are hereby notified that any dissemination, distribution or
copying of this communication is strictly prohibited. If you have received this
communication in error, please immediately notify the [EMAIL PROTECTED] and
destroy the original message.
***********************************************************************************
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
