Getting minimum value - 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

General SQL Server Forums

New to SQL Server Programming

Getting minimum value

Author

Topic

dreamzor
Starting Member

6 Posts

Posted - 2010-02-26 : 02:13:43

Hi.
I got a table that look something like this :

column1(varchar),column2(int),column3(varchar),column4(int)

the content of the table :

xx , 12 , somevalue , 49
xx , 24 , somevalue , 10
yy , 12 , somevalue , 10
yy , 24 , somevalue , 49
.......
If I want to select these rows

xx,12,somevalue,49
yy,13,somevalue,10

As you can see i want to select the rows with the lowest value in the second column.

How do I make this happen ?

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-02-26 : 02:16:11

What is the pattern of data after ......
because its not clear what exactly you want in output

Vabhav T

dreamzor
Starting Member

6 Posts

Posted - 2010-02-26 : 02:19:46

the pattern continues like this

xx,48,somevalue,108
zz,12,somevalue,48

and so on.

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-02-26 : 02:23:23

Still its not any pattern because
next of
xx, 12, somevalue, 49
is
xx , 24 , somevalue , 10
wht is the relation...

Vabhav T

dreamzor
Starting Member

6 Posts

Posted - 2010-02-26 : 02:28:05

The relation is that the first column is the same.
The only thing that changes in the rows is the other column values

after xx,12,somevalune,49
it should be xx,24,somevalue,(Higher value than 49)

Dont know how to explain it more to you, sorry

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-02-26 : 02:35:03

You can just order by column 2 and column 4 in ascending order...

Vabhav T

dreamzor
Starting Member

6 Posts

Posted - 2010-02-26 : 02:39:19

Yes, but then I get all the other rows as well, I only want the rows with the lowest values in the second column

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-02-26 : 02:44:50

Then just order by column 2...

Vabhav T

dreamzor
Starting Member

6 Posts

Posted - 2010-02-26 : 02:48:22

I dont think you understand me.

If the table contains these rows :

xx,12,somevalue,100
xx,24,somevalue,200
xx,36,somevalue,300
yy,12,somevalue,100
yy,24,somevalue,200
yy,36,somevalue,300

If I only want to select the rows with the lowest value in the second column, the rows I want in the ouput is :

xx,12,somevalue,100
yy,12,somevalue,100

If I only do a order by I get the other rows aswell, and that I dont want

Sachin.Nand

2937 Posts

Posted - 2010-02-26 : 02:53:11

If you are using SQL 2005 then


select * from
(
select *,row_number()over(partition by column1 order by column2 )rowid from yourtable
)t where rowid=1

PBUH

dreamzor
Starting Member

6 Posts

Posted - 2010-02-26 : 02:56:37

Thanks, that worked.

Sachin.Nand

2937 Posts

Posted - 2010-02-26 : 02:59:06

quote:
Originally posted by dreamzor

Thanks, that worked.

You are welcome

PBUH

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-01 : 01:02:59

Also see how effectively you can use row_number() function for various purposes
http://beyondrelational.com/blogs/madhivanan/archive/2007/08/27/multipurpose-row-number-function.aspx

Madhivanan

Failing to plan is Planning to fail

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2010-03-01 : 01:12:24

You should consider RANK instead of ROW_NUMBER to cater for the situation where more than one row has the lowest number within the partition.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources