SQL selecting unique records - 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

SQL selecting unique records

Author

Topic

angela_g
Starting Member

17 Posts

Posted - 2014-03-21 : 07:40:37

Hi All

I am trying to create a select query similar to the following but the problem I am having is that I want to only select one record where there may be several with the same dw_order_no. Can anyone help please? I have tried various ways using SQL developer but without success

SELECT VE_EZP_ORDER_TRANS.EZP_BILL_STATUS AS EZP_BILL_STATUS1,
VE_EZP_AGED_CUSTOMER_DEBT.SURNAME,
VE_EZP_AGED_CUSTOMER_DEBT.DEBT_AGE_CATEGORY,
VE_EZP_AGED_CUSTOMER_DEBT.DEBT_AGE,
VE_ORDERLINE.DW_ORDER_NO,
VE_ORDERLINE.PRD_SKN_NO,
VE_ORDERLINE.ORDER_DATE
FROM VE_ORDERLINE
INNER JOIN VE_EZP_ORDER_TRANS
ON VE_ORDERLINE.DW_ORDER_NO = VE_EZP_ORDER_TRANS.DW_ORDER_NO
INNER JOIN VE_EZP_AGED_CUSTOMER_DEBT
ON VE_EZP_ORDER_TRANS.DW_ORDER_NO = VE_EZP_AGED_CUSTOMER_DEBT.DW_ORDER_NO
WHERE VE_ORDERLINE.PRD_SKN_NO = '400063'
AND VE_ORDERLINE.ORDER_DATE >= TO_DATE('2013-03-01', 'YYYY-MM-DD')
GROUP BY VE_ORDERLINE.DW_ORDER_NO
HAVING (COUNT (VE_ORDERLINE.DW_ORDER_NO) > 1);

Here is the error message I receive
ORA-00979: not a GROUP BY expression
00979. 00000 - "not a GROUP BY expression"
*Cause:
*Action:
Error at Line: 1 Column: 8

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-03-21 : 08:33:10

This forum is for Microsoft SQL Server; so there are few if any who have expertise on Oracle and SQL Developer. You might try dbforums.com

If you were on Microsoft SQL Server, using the row_number function would be one way to achieve what you are trying to do.

angela_g
Starting Member

17 Posts

Posted - 2014-03-21 : 08:37:03

ok thank you!

Monib
Starting Member

11 Posts

Posted - 2014-03-24 : 03:35:35

SELECT VE_EZP_ORDER_TRANS.EZP_BILL_STATUS AS EZP_BILL_STATUS1,
VE_EZP_AGED_CUSTOMER_DEBT.SURNAME,
VE_EZP_AGED_CUSTOMER_DEBT.DEBT_AGE_CATEGORY,
VE_EZP_AGED_CUSTOMER_DEBT.DEBT_AGE,
VE_ORDERLINE.DW_ORDER_NO,
VE_ORDERLINE.PRD_SKN_NO,
VE_ORDERLINE.ORDER_DATE
FROM VE_ORDERLINE
INNER JOIN VE_EZP_ORDER_TRANS
ON VE_ORDERLINE.DW_ORDER_NO = VE_EZP_ORDER_TRANS.DW_ORDER_NO
INNER JOIN VE_EZP_AGED_CUSTOMER_DEBT
ON VE_EZP_ORDER_TRANS.DW_ORDER_NO = VE_EZP_AGED_CUSTOMER_DEBT.DW_ORDER_NO
WHERE VE_ORDERLINE.PRD_SKN_NO = '400063'
AND VE_ORDERLINE.ORDER_DATE >= TO_DATE('2013-03-01', 'YYYY-MM-DD')
AND VE_ORDERLINE.ORDER_DATE IN
(select VE_ORDERLINE.ORDER_DATE frin VE_ORDERLINE group by VE_ORDERLINE) having count(VE_ORDERLINE) > 1)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources