OraFAQ Forum: SQL & PL/SQL » Getting Unique Results

Home » SQL & PL/SQL » SQL & PL/SQL » Getting Unique Results

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Getting Unique Results [message #619460]

Tue, 22 July 2014 14:26

itcoracle
Messages: 3
Registered: July 2014

Junior Member

I am using the following query to get a bunch of data:

SELECT DISTINCT table2.xid, table1.date from table1 LEFT JOIN table2 ON table1.id = table2.id
ORDER BY table2.xid ASC, table1.date DESC
Results:
table2.xid  table1.date
101          7/21/14
102          7/21/14
102          6/26/14
102          5/05/14
103          6/18/14
104          5/01/14
104          3/19/14

What I can't figure out how to do is limit the results to only show the rows with unique XIDs and each XID is associated with the latest dates. Meaning, if an XID has multiple dates associated with it, only show the row of data with the latest date giving the following results.

Desired Results:
table2.xid  table1.date
101          7/21/14
102          7/21/14
103          6/18/14
104          5/01/14

I need to do this with a single query too. In other words, I can't use any other language to provide the logic to sort the data. I need to feed one query to the db and have it spit out the above results.
Any Ideas?

Report message to a moderator

Re: Getting Unique Results [message #619463 is a reply to message #619460]

Tue, 22 July 2014 14:42

msashi
Messages: 3
Registered: July 2014
Location: United States

Junior Member

You can do a group by table2.xid and get max(table1.date).

Report message to a moderator

Re: Getting Unique Results [message #619473 is a reply to message #619463]

Tue, 22 July 2014 16:42

itcoracle
Messages: 3
Registered: July 2014

Junior Member

Ok so I over-simplified my query for the purposes of my question thinking it wouldn't matter but apparently it does, so let me try this again. Sorry!

The MAX and GROUP BY work just fine in my original example, however, I have a nested JOIN statement and when I add it to the mix, it breaks:

Works without the GROUP just shows me rows I don't need:
SELECT DISTINCT
    table1.xid,
    table2.date,
    table3.description
FROM table1
LEFT JOIN (table2 LEFT JOIN table3 ON table2.pid = table3.pid)
  ON table1.id = table3.id
ORDER BY table1.xid ASC, table2.date DESC

Perhaps I am putting it in the wrong place but I have tried several different ways and I'm not sure what is wrong:

With the MAX and GROUP BY but breaks: "not a GROUP BY expression"
SELECT DISTINCT
    table1.xid,
    MAX(table2.date),
    table3.description
FROM table1
LEFT JOIN (table2 LEFT JOIN table3 ON table2.pid = table3.pid)
  ON table1.id = table3.id
  GROUP BY table1.xid
ORDER BY table1.xid ASC, table2.date DESC

In order for me to not get the error, I have to add all of the selected items to the GROUP BY statement (GROUP BY table1.xid, table2.date, table3.description) which makes it ineffective.

Any ideas?

Report message to a moderator

Re: Getting Unique Results [message #619476 is a reply to message #619473]

Tue, 22 July 2014 16:52

superboy87
Messages: 3
Registered: May 2014

Junior Member

I think just adding table3.description to the group by should work.

GROUP BY table1.xid, table3.description

Report message to a moderator

Re: Getting Unique Results [message #619499 is a reply to message #619476]

Wed, 23 July 2014 01:23

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

The query is not correct and will not work if some id are missing in table2 and/or tables3.
You must push the MAX into a subquery.

Report message to a moderator

Re: Getting Unique Results [message #619585 is a reply to message #619473]

Wed, 23 July 2014 10:55

LKBrwn_DBA
Messages: 487
Registered: July 2003
Location: WPB, FL

Senior Member

And perhaps you need to remove the "DISTINCT" clause from your query as you are using the "GROUP BY" option.

[Updated on: Wed, 23 July 2014 10:57] by Moderator

Report message to a moderator

Re: Getting Unique Results [message #619587 is a reply to message #619585]

Wed, 23 July 2014 11:14

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

As Michel suggested, I think you MIGHT look for an outer join.

One suggestion, if you are a developer, spend some time on preparing unit test cases, it will lead you to probe yourself to think critical cases with your query/process. It would just not help you as a developer, but also the testers to raise less defects Smile

Report message to a moderator

Re: Getting Unique Results [message #619598 is a reply to message #619499]

Wed, 23 July 2014 12:44

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Something like that:

SELECT table1.xid, table2.max_date, table3.description
FROM   table1
     LEFT JOIN 
       (select table2.pid, max(table2.date) max_date from table2 group by table2.pid) table2
         ON table2.pid = table1.id
     LEFT JOIN 
       table3 
         ON table3.pid = table1.id
ORDER BY table1.xid ASC, table2.max_date DESC

But, of course, as we don't know the actual tables definition and their relations (above all on these id, xid, pid, I wonder if the "table2.pid = table1.id" you posted is not "table2.pid = table1.xid"), it may not be exactly that.

[Updated on: Wed, 23 July 2014 12:45]

Report message to a moderator

Re: Getting Unique Results [message #619623 is a reply to message #619598]

Wed, 23 July 2014 14:24

Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA

Senior Member

In general, your solution will not return single row per table1.xid. We don't know if table1 to table2 relationship is one-to-one or one-to-many or many-to-many. We don't know if table1 to table3 relationship is one-to-one or one-to-many or many-to-many. So generic solution would be:

SELECT  table1.xid,
        max(table2.date) max_date,
        max(table3.description) keep(dense_rank last order by table2.date) description
  FROM      table1
        LEFT JOIN 
           table2
         ON table2.pid = table1.id
        LEFT JOIN 
            table3 
         ON table3.pid = table1.id
  ORDER BY table1.xid
/

SY.

Report message to a moderator

Re: Getting Unique Results [message #619643 is a reply to message #619623]

Wed, 23 July 2014 16:56

itcoracle
Messages: 3
Registered: July 2014

Junior Member

I want to say Thank You to everyone who replied to this post.

I am not a developer. Just an equipment rental warehouse manager who was told create a report with very specific data. Normally I would have to run 5 different custom reports and consolidate them in Excel (usually takes an entire week of deleting, copying and pasting). I was sick of it and I have received no help from our IT dept or the database software helpdesk (we pay enough for the damn thing you'd think they'd return an email or phone call). Anyway, I found a place in the database's configuration module where I could throw SQL select statements at the database and so I've been teaching myself how to write sql code so I can gather all of this data from 5 different tables into one exportable dataset.

End Result: 28 Columns and 7268 Rows

Putting together the information from all of your responses and a day full of googling and reading through the Oracle pdf documentation manual I downloaded, I have finally been able to make it work. With any luck I'll never have to look at sql code again!

Thanks again!

Report message to a moderator

Re: Getting Unique Results [message #619645 is a reply to message #619643]

Wed, 23 July 2014 17:00

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

I applaud your resourcefulness & willingness to learn something new!

Report message to a moderator

Re: Getting Unique Results [message #619678 is a reply to message #619623]

Thu, 24 July 2014 06:09

Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA

Senior Member

Oops, I missed GROUP BY clause:

SELECT  table1.xid,
        max(table2.date) max_date,
        max(table3.description) keep(dense_rank last order by table2.date) description
  FROM      table1
        LEFT JOIN 
           table2
         ON table2.pid = table1.id
        LEFT JOIN 
            table3 
         ON table3.pid = table1.id
  GROUP BY table1.xid
  ORDER BY table1.xid
/

SY.

Report message to a moderator

Previous Topic:	Create a select statement for below scenario
Next Topic:	Compare SUM of column to another table (column)

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Apr 24 23:22:00 CDT 2024