Query help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Query help

Author

Topic

cipriani1984
Constraint Violating Yak Guru

304 Posts

Posted - 2009-02-20 : 09:50:50

Hi,

I have a table with the following values.

Cust, code1, value, desc
1, 2, 55, sale
1, 2, 80, buy
2, 3, 135, sale
2, 5, 53, buy

I would like to select the top row for each customer, so i would like customer 1's row that has code 2 and value 80, and cust 2 that has code 3 and value 135. This will happen with alot of rows.

I just want to come out with the highest value along with the code1,

I tried MAX(value) but obv this means I have to group all the other fields returned in the query but this brought issues.

Any other ways?

Much appreciated.

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-20 : 09:53:21

[code]
SELECT Cust, code1, value, desc
FROM
(
SELECT ROW_NUMBER() OVER(PARTITION BY Cust ORDER BY code1,value) AS Seq,Cust, code1, value, desc
FROM Yourtable
)t
WHERE Seq=1
[/code]

mfemenel
Professor Frink

1421 Posts

Posted - 2009-02-20 : 09:56:21

Create table #test(Cust int, code1 int, value int, descr varchar(10))
Insert into #test(Cust,code1,value,descr) values(1,2,55,'sale'),(1,2,80,'buy'),(2,3,135,'sale'),(2,5,53,'buy')

select t.*
from #test t
inner join (select Cust,MAX(value) as value
from #test
Group BY Cust)C
ON t.Cust=c.Cust
AND t.value=C.value

Mike
"oh, that monkey is going to pay"

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-20 : 09:59:28

shouldn't it be like this?

quote:

Originally posted by visakh16


SELECT Cust, code1, value, desc
FROM
(
SELECT ROW_NUMBER() OVER(PARTITION BY Cust ORDER BY value desc) AS Seq,Cust, code1, value, desc
FROM Yourtable
)t
WHERE Seq=1

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-20 : 10:03:49

i had thought of same thing, but still unclear what OP meant over this

highest value along with the code1
not sure he meant if he needs to consider code1 also for getting mx or just value alone

cipriani1984
Constraint Violating Yak Guru

304 Posts

Posted - 2009-02-20 : 10:05:58

Thanks for the prompt reply!

This is my new query

SELECT student_id, Levelcodes, GLH, NewCode
FROM (SELECT ROW_NUMBER() OVER (PARTITION BY student_id
ORDER BY NewCode, GLH) AS Seq, NewCode, GLH, Levelcodes
FROM aaa_prog0809) t
WHERE Seq = 1

But I get sql error? invalid column name 'student_id'??

quote:

Originally posted by visakh16


SELECT Cust, code1, value, desc
FROM
(
SELECT ROW_NUMBER() OVER(PARTITION BY Cust ORDER BY code1,value) AS Seq,Cust, code1, value, desc
FROM Yourtable
)t
WHERE Seq=1

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-20 : 10:07:41

do you have such a column in your table?

cipriani1984
Constraint Violating Yak Guru

304 Posts

Posted - 2009-02-20 : 10:08:01

I basically need the highest value to come along with code1

when i do

select distinct cust, MAX(value)

it works on the first cust, but the 2nd cust has lower code1 and higher value.

quote:
Originally posted by visakh16

i had thought of same thing, but still unclear what OP meant over this

highest value along with the code1
not sure he meant if he needs to consider code1 also for getting mx or just value alone

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-20 : 10:08:43

quote:
Originally posted by cipriani1984

Thanks for the prompt reply!

This is my new query

SELECT student_id, Levelcodes, GLH, NewCode
FROM (SELECT ROW_NUMBER() OVER (PARTITION BY student_id
ORDER BY NewCode, GLH) AS Seq,student_id NewCode, GLH, Levelcodes
FROM aaa_prog0809) t
WHERE Seq = 1

But I get sql error? invalid column name 'student_id'??

quote:
Originally posted by visakh16

SELECT Cust, code1, value, desc
FROM
(
SELECT ROW_NUMBER() OVER(PARTITION BY Cust ORDER BY code1,value) AS Seq,Cust, code1, value, desc
FROM Yourtable
)t
WHERE Seq=1

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-20 : 10:11:55

quote:
Originally posted by cipriani1984

I basically need the highest value to come along with code1

when i do

select distinct cust, MAX(value)

it works on the first cust, but the 2nd cust has lower code1 and higher value.

quote:
Originally posted by visakh16

i had thought of same thing, but still unclear what OP meant over this

highest value along with the code1
not sure he meant if he needs to consider code1 also for getting mx or just value alone

you mean highest value for every code values?or record with highest value for each customer?

cipriani1984
Constraint Violating Yak Guru

304 Posts

Posted - 2009-02-20 : 10:13:59

basically bring back a single row for customer with highest value, bring back with him the code1 and desc.

quote:
Originally posted by visakh16

quote:
Originally posted by cipriani1984

I basically need the highest value to come along with code1

when i do

select distinct cust, MAX(value)

it works on the first cust, but the 2nd cust has lower code1 and higher value.

quote:
Originally posted by visakh16

i had thought of same thing, but still unclear what OP meant over this

highest value along with the code1
not sure he meant if he needs to consider code1 also for getting mx or just value alone

you mean highest value for every code values?or record with highest value for each customer?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-20 : 10:18:00

ok. then use last suggestion given by Sodeep on 02/20/2009 : 10:08:43 .

SQLforGirls
Starting Member

48 Posts

Posted - 2009-02-20 : 10:20:42

What about this:

select cust, code1, value, desc
from table1 t1
where value = (select max(value) from table1 t2
where t2.cust = t1.cust)

cipriani1984
Constraint Violating Yak Guru

304 Posts

Posted - 2009-02-20 : 10:21:10

Thanks guys!

quote:
Originally posted by visakh16

ok. then use last suggestion given by Sodeep on 02/20/2009 : 10:08:43 .

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-20 : 10:25:06

quote:
Originally posted by cipriani1984

Thanks guys!

quote:
Originally posted by visakh16

ok. then use last suggestion given by Sodeep on 02/20/2009 : 10:08:43 .

Good job.

cipriani1984
Constraint Violating Yak Guru

304 Posts

Posted - 2009-02-20 : 10:28:11

Actually that didnt work... it just took the top row without calculating which is higher value etc.

quote:
Originally posted by sodeep

quote:
Originally posted by cipriani1984

Thanks guys!

quote:
Originally posted by visakh16

ok. then use last suggestion given by Sodeep on 02/20/2009 : 10:08:43 .

Good job.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-20 : 10:38:53

What is expected output in above sample? Please show us clearly.

cipriani1984
Constraint Violating Yak Guru

304 Posts

Posted - 2009-02-20 : 10:53:18

Its fine ive sorted it, when i put this query into a report. I get the following error:

ExecuteReader: CommandText property has not been initialized.

quote:
Originally posted by sodeep

What is expected output in above sample? Please show us clearly.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-20 : 11:09:38

Did you search for error?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-20 : 11:20:30

quote:
Originally posted by cipriani1984

Its fine ive sorted it, when i put this query into a report. I get the following error:

ExecuteReader: CommandText property has not been initialized.

quote:
Originally posted by sodeep

What is expected output in above sample? Please show us clearly.

where are you putting this?

SQLforGirls
Starting Member

48 Posts

Posted - 2009-02-20 : 13:48:46

Did anyone try my solution? Just curious.

select cust, code1, value, desc
from table1 t1
where value = (select max(value) from table1 t2
where t2.cust = t1.cust)
Go to Top of Page

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources