Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

need an urgent help to find max records

Status
Not open for further replies.
chamilz

chamilz

MIS
Joined
Feb 10, 2005
Messages
102
Location
CA
i have data set like this...
need to find col1,col2,col3...col8 for highest value of col8 i.e max(col8)..thanks in advance/
col1 col2 col3 col4 col5 col6 col7 col8
48019 459907989 308 1 920 250 11060 03259
48019 459907989 308 1 920 388 12239 03335
48019 459907989 308 2 930 250 11060 03259
48019 459907989 308 2 930 388 12239 03335
48019 459907989 308 3 960 250 11060 03259
48019 459907989 308 3 960 388 12239 03335
48019 459907989 308 4 950 250 11060 03259
48019 459907989 308 4 950 388 12239 03335
 
Sort by date Sort by votes
Code: 
Select col1, col2, col3, col4, col5, col6, col7, col8
from table 
where col8 = (Select max(col8) from table)

Does that help?



Catadmin - MCDBA, MCSA
Beware the error of pre-emptive poultry inventory!
 
Upvote 0 Downvote
This should do it....
Code: 
SELECT col1, col2, ... col8
FROM mytable
WHERE col8 = (SELECT MAX(col8) FROM mytable)
-SQLBill

Posting advice: FAQ481-4875
 
Upvote 0 Downvote
Dang...beat me by a less than a minute.

-SQLBill

Posting advice: FAQ481-4875
 
Upvote 0 Downvote
Hee, Hee!!!

I'm finally starting to get the hang of this T-SQL thing. @=)



Catadmin - MCDBA, MCSA
Beware the error of pre-emptive poultry inventory!
 
Upvote 0 Downvote
I tried this, but this gave only one set of record...
i just posted sample set of records in my table..

just to give more idea....
col1- style id
col2-style code
col3 - color
col4 - quanity
col5 - size
col7 - vendor
col8 - pack code (pack has a combination of col1,col2,col3..col7)

i want to list all the style,color,size,quanity combination for highest pack code(col8)

i have n number of records...
Thanks,
 
Upvote 0 Downvote
Sorry, misunderstood your request...just drop the DISTINCT keyword.

-SQLBill

Posting advice: FAQ481-4875
 
Upvote 0 Downvote
Whooops, just realized there ISN'T any DISTINCT keyword in either mine nor CatAdmin's post.

Our script should return:

col1 col2 col3 col4 col5 col6 col7 col8
48019 459907989 308 1 920 388 12239 03335
48019 459907989 308 2 930 388 12239 03335
48019 459907989 308 3 960 388 12239 03335
48019 459907989 308 4 950 388 12239 03335

-SQLBill

Posting advice: FAQ481-4875
 
Upvote 0 Downvote
yes this gave me only one set of records....but my table has records like this / appriciate your help

48019 459907989 002 1 920--- 01677
48019 459907989 002 1 920 03406
48019 459907989 002 2 930 01677
48019 459907989 002 2 930 03406
48019 459907989 002 3 950 01677
48019 459907989 002 3 950 03406
48019 459907989 002 3 960 01677
48019 459907989 002 3 960----max 03406 03406
48019 459907989 101 1 920 00157
48019 459907989 101 1 920 03405
48019 459907989 101 2 930 00157
48019 459907989 101 2 930 03405
48019 459907989 101 3 950 00157
48019 459907989 101 3 950 03405
48019 459907989 101 3 960 00157
48019 459907989 101 3 960--max 03405 03405
48019 459907989 201 1 920--- 01383
48019 459907989 201 1 920 03410
48019 459907989 201 2 930 01383
48019 459907989 201 2 930 03410
48019 459907989 201 3 950 01383
48019 459907989 201 3 950 03410
48019 459907989 201 3 960 01383
48019 459907989 201 3 960--max 03410 etc.. 03410
48019 459907989 203 1 920 00330
48019 459907989 203 2 930 00330
48019 459907989 203 3 950 00330
48019 459907989 203 3 960 00330
48019 459907989 215 1 920 01707
48019 459907989 215 1 920 03409
48019 459907989 215 2 930 01707
48019 459907989 215 2 930 03409
48019 459907989 215 3 950 01707
48019 459907989 215 3 950 03409
48019 459907989 215 3 960 01707
48019 459907989 215 3 960 03409
48019 459907989 302 1 920 01695
48019 459907989 302 2 930 01695
48019 459907989 302 3 950 01695
48019 459907989 302 3 960 01695
48019 459907989 305 1 920 01337
48019 459907989 305 1 920 03408
48019 459907989 305 2 930 01337
48019 459907989 305 2 930 03408
48019 459907989 305 3 950 01337
48019 459907989 305 3 950 03408
48019 459907989 305 3 960 01337
48019 459907989 305 3 960 03408
48019 459907989 307 1 920 01380
48019 459907989 307 2 930 01380
48019 459907989 307 3 950 01380
48019 459907989 307 3 960 01380
48019 459907989 308 1 920 03259
48019 459907989 308 1 920 03335
48019 459907989 308 2 930 03259
48019 459907989 308 2 930 03335
48019 459907989 308 3 960 03259
48019 459907989 308 3 960 03335
48019 459907989 308 4 950 03259
48019 459907989 308 4 950 03335
48019 459907989 401 1 920 01714
48019 459907989 401 2 930 01714
48019 459907989 401 3 950 01714
48019 459907989 401 3 960 01714
48019 459907989 403 1 920 01339
48019 459907989 403 2 930 01339
48019 459907989 403 3 950 01339
48019 459907989 403 3 960 01339
48019 459907989 404 1 920 01681
48019 459907989 404 2 930 01681
48019 459907989 404 3 950 01681
48019 459907989 404 3 960 01681
48019 459907989 405 1 920 01362
48019 459907989 405 2 930 01362
48019 459907989 405 3 950 01362
48019 459907989 405 3 960 01362
48019 459907989 406 1 920 03262
48019 459907989 406 1 920 03333
48019 459907989 406 2 930 03262
48019 459907989 406 2 930 03333
48019 459907989 406 3 960 03262
48019 459907989 406 3 960 03333
48019 459907989 406 4 950 03262
48019 459907989 406 4 950 03333
48019 459907989 412 1 920 03258
48019 459907989 412 1 920 03331
48019 459907989 412 2 930 03258
48019 459907989 412 2 930 03331
48019 459907989 412 3 960 03258
48019 459907989 412 3 960 03331
48019 459907989 412 4 950 03258
48019 459907989 412 4 950 03331
48019 459907989 418 1 920 01710
48019 459907989 418 2 930 01710
48019 459907989 418 3 950 01710
48019 459907989 418 3 960 01710
48019 459907989 503 1 920 01691
48019 459907989 503 2 920 00076
48019 459907989 503 2 930 01691
48019 459907989 503 3 930 00076
48019 459907989 503 3 950 01691
48019 459907989 503 3 960 01691
48019 459907989 503 4 960 00076
48019 459907989 603 1 920 03261
48019 459907989 603 1 920 03327
48019 459907989 603 2 930 03261
48019 459907989 603 2 930 03327
48019 459907989 603 3 960 03261
48019 459907989 603 3 960 03327
48019 459907989 603 4 950 03261
48019 459907989 603 4 950 03327
48019 459907989 605 1 920 01356
48019 459907989 605 2 930 01356
48019 459907989 605 3 950 01356
48019 459907989 605 3 960 01356
48019 459907989 610 1 920 01033
48019 459907989 610 2 930 01033
48019 459907989 610 3 950 01033
48019 459907989 610 3 960 01033
48019 459907989 611 1 920 03260
48019 459907989 611 1 920 03329
48019 459907989 611 2 930 03260
48019 459907989 611 2 930 03329
48019 459907989 611 3 960 03260
48019 459907989 611 3 960 03329
48019 459907989 611 4 950 03260
48019 459907989 611 4 950 03329
48019 459907989 612 1 920 01387
48019 459907989 612 1 920 03407
48019 459907989 612 2 930 01387
48019 459907989 612 2 930 03407
48019 459907989 612 3 950 01387
48019 459907989 612 3 950 03407
48019 459907989 612 3 960 01387
48019 459907989 612 3 960 03407
48019 459907989 802 1 920 01446
48019 459907989 802 2 930 01446
48019 459907989 802 3 950 01446
48019 459907989 802 3 960 01446
48019 459907989 804 1 920 01705
48019 459907989 804 2 930 01705
48019 459907989 804 3 950 01705
48019 459907989 804 3 960 01705
48019 459907989 805 1 920 01701
48019 459907989 805 2 930 01701
48019 459907989 805 3 950 01701
48019 459907989 805 3 960 01701
 
Upvote 0 Downvote
Chamilz,

Please don't post the same problem twice. You could have just posted your reply here in this thread instead of doing it here AND opening up a new thread.

Check your other thread. Someone seems to have found a solution. And try not to do this again as it tends to only confuse the issue when people are trying to help you.

Thanks,



Catadmin - MCDBA, MCSA
Beware the error of pre-emptive poultry inventory!
 
Upvote 0 Downvote
Got your point Catadmin...i am new to this forum..thanks for your advice..
 
Upvote 0 Downvote
Chamilz,

Just to let you know, what you ended up asking for wasn't the same as what you first described. This is why it's always best to include enough sample data and what results you want to see.

Your first post just asked for the MAX value from col8 and all the rest of the columns where col8 matched that value. That's what we provided you. If we had known that you wanted the MAX value from col5 for each value in col8 and everything related, we could have come up with the answer sooner.

-SQLBill

Posting advice: FAQ481-4875
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

V
  • Locked
  • Question Question
xp_cmdshell vs psexec - this SQL server forgets credential to an W10 share
Replies
0
Views
1K
Volvere
V
f0rg3tfu1
  • Locked
  • Question Question
PLSQL Package Help
Replies
0
Views
737
f0rg3tfu1
f0rg3tfu1
WomanPro
  • Locked
  • Question Question
Insert into query adds records to unreadable format, how do I change that?
Replies
5
Views
825
Chris Miller
Chris Miller
peac3
  • Locked
  • Question Question
split one row into multiple rows... help pls
Replies
12
Views
2K
Chris Miller
Chris Miller
Cpreston
  • Locked
  • Question Question
Seconds to minutes
Replies
5
Views
2K
Chris Miller
Chris Miller

Part and Inventory Search

Sponsor

Back
Top