|
View previous topic :: View next topic
|
| Author |
Message |
tushar_study
New User
Joined: 01 Feb 2007
Posts: 30
Location: Mumbai
|
|
|
|
i have the following table structure
ACCT_ID SEQ_NR
81256382 9999
81256382 1
81256382 3
79350011 9999
79350011 1
79350011 2
for each of the account id's I need to retrieve the row with max SEQ_NR
but row with SEQ_NR 9999 should not be retrieved.
PLease help me with the SQL query |
|
| Back to top |
|
 |
murmohk1
Senior Member
.jpg)
Joined: 29 Jun 2006
Posts: 1436
Location: Bangalore,India
|
|
| Back to top |
|
|
tushar_study
New User
Joined: 01 Feb 2007
Posts: 30
Location: Mumbai
|
|
|
|
Hi MMohan,
thanks for the reply ...actually i m looking for sql using JOINS...can u give me an idea that if i dont wanna include 9999 then can i give SEQ_NR <> 9999 in the where clause of the join
Thanks and Regards,
Tushar |
|
| Back to top |
|
|
the_gautam
Active User
Joined: 05 Jun 2005
Posts: 165
Location: Bangalore
|
|
|
|
Hi tushar,
i ve not executed this query however, you may check it out :
SELECT ACCT_ID, SEQ_NR FROM MYTABLE A WHERE SEQ_NR = (SELECT MAX(SEQ_NR) FROM MYTABLE B WHERE SEQ_NR <> 9999 AND A.SEQ_NR=B.SEQ_NR); |
|
| Back to top |
|
|
stodolas
Active Member
Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin
|
|
|
|
May need some tweeking.
| Code: |
--This grabs the max regardless of 9999 or not
SELECT acct_id, max(seq_nr) as max_nr
FROM table
GROUP BY acct_id
--Try this from a performance standpoint, I don't know if it will work
SELECT acct_id, max(seq_nr) as max_nr
FROM table
WHERE seq_nr <> 9999
GROUP BY acct_id
--This should work but may be bad for performance
SELECT A.acct_id, max(A.seq_nr)
FROM
(SELECT *
FROM table
WHERE seq_nr <> 9999) A
GROUP BY A.acct_id
|
|
|
| Back to top |
|
|
|
|
