471,075 Members | 1,284 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,075 software developers and data experts.

SQL to retrieve min value of the combination

Hi,

I have the following table

1001 2 3
1001 3 5
1001 5 7
1002 5 4
1002 6 2

I need to get a result

1001 2 3
1002 6 2

How is it possible ?
Jul 19 '05 #1
3 10777
question's a little vague, but assuming you are trying to get records for
the minimum values of the first and third column, for all rows in a table,
you could try a subquery like

select *
from x
where (col1, col3) in (
select col1, min(col3)
from x
group by col1
)

but don't try this until you understand why it works ;-)

-- mcs
"Ravindra Harve" <rh****@hotmail.com> wrote in message
news:83**************************@posting.google.c om...
| Hi,
|
| I have the following table
|
| 1001 2 3
| 1001 3 5
| 1001 5 7
| 1002 5 4
| 1002 6 2
|
| I need to get a result
|
| 1001 2 3
| 1002 6 2
|
| How is it possible ?
Jul 19 '05 #2
Use this one , the same but easier
select *
from (
select col1, min(col3)
from x
group by col1
)

"Mark C. Stock" <mcstockX@Xenquery .com> wrote in message
news:D_********************@comcast.com...
question's a little vague, but assuming you are trying to get records for
the minimum values of the first and third column, for all rows in a table,
you could try a subquery like

select *
from x
where (col1, col3) in (
select col1, min(col3)
from x
group by col1
)

but don't try this until you understand why it works ;-)

-- mcs
"Ravindra Harve" <rh****@hotmail.com> wrote in message
news:83**************************@posting.google.c om...
| Hi,
|
| I have the following table
|
| 1001 2 3
| 1001 3 5
| 1001 5 7
| 1002 5 4
| 1002 6 2
|
| I need to get a result
|
| 1001 2 3
| 1002 6 2
|
| How is it possible ?

Jul 19 '05 #3
Julia,

Not the same by any means.

Your select * does absolutely nothing -- the subquery results are simply
passed thru unchanged

Your subquery contains only two columns -- the assumption is that all
columns should be displayed

Try them both on the EMP table with the empno, deptno, and sal in the
resultset and dept and sal in the subquery ("Write a report that lists the
lowest paid employees in each department")

-- mcs

"Julia Sats" <ju********@sympatico.ca> wrote in message
news:iK*********************@news20.bellglobal.com ...
| Use this one , the same but easier
| select *
| from (
| select col1, min(col3)
| from x
| group by col1
| )
|
| "Mark C. Stock" <mcstockX@Xenquery .com> wrote in message
| news:D_********************@comcast.com...
| > question's a little vague, but assuming you are trying to get records
for
| > the minimum values of the first and third column, for all rows in a
table,
| > you could try a subquery like
| >
| > select *
| > from x
| > where (col1, col3) in (
| > select col1, min(col3)
| > from x
| > group by col1
| > )
| >
| > but don't try this until you understand why it works ;-)
| >
| > -- mcs
| >
| >
| > "Ravindra Harve" <rh****@hotmail.com> wrote in message
| > news:83**************************@posting.google.c om...
| > | Hi,
| > |
| > | I have the following table
| > |
| > | 1001 2 3
| > | 1001 3 5
| > | 1001 5 7
| > | 1002 5 4
| > | 1002 6 2
| > |
| > | I need to get a result
| > |
| > | 1001 2 3
| > | 1002 6 2
| > |
| > | How is it possible ?
| >
| >
|
|
Jul 19 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by forums_mp | last post: by
1 post views Thread by Eugfene | last post: by
4 posts views Thread by marco.nl | last post: by
4 posts views Thread by jay | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.