SQL HELP

[puneet]

HI

I have a table called TEST which is like

ORDERTYPE ORDER_DATE NAME
---------- --------- -----------------------------
x 10-AUG-00 PUNEET
x 11-AUG-00 ssss
y 10-AUG-00 kkkk
y 11-AUG-00 PPPP


I want to find out the * from this table for each distinct order_type where order_date is min.

eg.

Answer for this will be be like

x 10-AUG-00 PUNEET
y 10-AUG-00 kkkk


Please let me know the SQL for this

[shawish_sababa]

select ORDERTYPE ORDER_DATE NAME
from table_name a
where ORDER_DATE = (select min(ORDER_DATE )
from table_name b
where a.ordertype = b.ordertype)

[puneet]

Thanks

This was great but I am still getting a difference between the distinct values of the ordertype and the count of this output .

Trying to find out where it is lacking

regards

[shawish_sababa]

Hi puneet

sorry, but i should have asked you of your ordr_date date formate. if it has no hours/minutes/seconds then if you have more then one order type per date, my select statment is not good for you! what you need to do is to add another condition (if you have column to us for this purpose) or, you can use min(rowid), but what you get is a remdom selection of line with thesame order date!

select min(rowid), ORDERTYPE, ORDER_DATE, NAME
from table_name a
where ORDER_DATE = (select min(ORDER_DATE )
from table_name b
where a.ordertype = b.ordertype)
group by ORDERTYPE, ORDER_DATE, NAME

[puneet]

Thanks !

You are awsome , It worked

regards