DISTINCT on one field - 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)

DISTINCT on one field

Author

Topic

helixpoint
Constraint Violating Yak Guru

291 Posts

Posted - 2008-11-12 : 19:28:48

How do you do a DISTINCT on one field in the select clause and just include other fields in the select clause, not making them distinct?

For example:

SELECT DISTINCT IndivOrders.DATE_INVC,
IndivOrders.ID_ORD, IndivOrders.ID_SHIP,
IndivOrders.ID_CUST_SOLDTO,
IndivOrders.ID_PO_CUST,
IndivOrders.TYPE_ORD_CP,
IndivOrders.SEQ_SHIPTO,
IndivOrders.ID_CUST_BILLTO,
IndivOrders.NAME_ORD_BY
INTO
DistinctIndivOrders
FROM IndivOrders

I just want Date_Invc to be distinct. I do not want the others to be distinct. If I use a group by, I get an error because the other information in the select clause is not in the aggregatte.

Dave
Helixpoint Web Development
http://www.helixpoint.com

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2008-11-12 : 22:00:58

This will return data, but if more than one record is there for IndivOrders.DATE_INVC
It may not get what you want.

SELECT
IndivOrders.DATE_INVC,
Max(IndivOrders.ID_ORD) as ID_ORD,
Max(IndivOrders.ID_SHIP) as ID_SHIP,
Max(IndivOrders.ID_CUST_SOLDTO) as ID_CUST_SOLDTO,
Max(IndivOrders.ID_PO_CUST) as ID_PO_CUST,
Max(IndivOrders.TYPE_ORD_CP) as TYPE_ORD_CP,
Max(IndivOrders.SEQ_SHIPTO) as SEQ_SHIPTO,
Max(IndivOrders.ID_CUST_BILLTO) as ID_CUST_BILLTO,
Max(IndivOrders.NAME_ORD_BY) as NAME_ORD_BY
INTO
DistinctIndivOrders
FROM IndivOrders
group by IndivOrders.DATE_INVC

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-12 : 23:11:56

quote:
Originally posted by helixpoint

How do you do a DISTINCT on one field in the select clause and just include other fields in the select clause, not making them distinct?

For example:

SELECT DISTINCT IndivOrders.DATE_INVC,
IndivOrders.ID_ORD, IndivOrders.ID_SHIP,
IndivOrders.ID_CUST_SOLDTO,
IndivOrders.ID_PO_CUST,
IndivOrders.TYPE_ORD_CP,
IndivOrders.SEQ_SHIPTO,
IndivOrders.ID_CUST_BILLTO,
IndivOrders.NAME_ORD_BY
INTO
DistinctIndivOrders
FROM IndivOrders

I just want Date_Invc to be distinct. I do not want the others to be distinct. If I use a group by, I get an error because the other information in the select clause is not in the aggregatte.

Dave
Helixpoint Web Development
http://www.helixpoint.com

when you make Date_Invc distinct, obviously you can only return a single value for all other fields along with it. in such cae, what according to your business rule should be returned values? should it be last records value or first records value? Some sample data will help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources