Solved

Query to return unique row based on column 1

Posted on 2013-12-24
7
420 Views
Last Modified: 2013-12-24
I'm a beginner with SQL and trying to develop a query that will return the last event logged on one of our databases against all of our clients.


Snippet of the table is below...
I'd like the first column to contain unique values, and the row returned should be the max of the event_ref column.
organisation_ref	event_date	create_user	event_ref
481570	23/12/2013	658840	61935080
481560	20/12/2013	554690	61931800
481560	20/12/2013	554690	61931840
481560	24/12/2013	7363780	61936480
481560	23/12/2013	7363780	61934200
481560	23/12/2013	7363780	61934740
481560	23/12/2013	7363780	61934330
481550	19/12/2013	7366260	61919410

Open in new window


Here is my SQL...
SELECT event.organisation_ref, event.event_date, event.create_user, MAX(event.event_ref) AS event_ref
FROM event
GROUP BY event.organisation_ref, event.event_date, event.create_user, event.event_ref
HAVING (((event.organisation_ref) Is Not Null))
ORDER BY event.organisation_ref DESC;

Where am I going wrong?
0
Comment
Question by:antonioking
  • 4
  • 2
7 Comments
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 39737693
you need event_ref with client mean Organization then try only this

SELECT event.organisation_ref, MAX(event.event_ref) AS event_ref
FROM event
GROUP BY event.organisation_ref, event.event_ref
HAVING (((event.organisation_ref) Is Not Null))
ORDER BY event.organisation_ref DESC;
0
 

Author Comment

by:antonioking
ID: 39737698
Thanks, but I want to include the event_date and create_user columns.

Regards
0
 
LVL 4

Expert Comment

by:rshq
ID: 39737699
Hi
 If you use Sql 2005 or higher please use this code

Select * from
(SELECT Row_number() over (partition by event.organisation_ref order by  event.event_date desc) as Row, event.organisation_ref,event.event_date,event.create_user, event.event_ref
FROM event where  event.organisation_ref Is Not Null) as a
where  (Row=1)

Open in new window

0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:antonioking
ID: 39737703
I'm using Microsoft Access 2010, will this work in Access?
0
 
LVL 39

Accepted Solution

by:
Pratima Pharande earned 500 total points
ID: 39737709
try this

SELECT event.organisation_ref, event.event_date, event.create_user, event.event_ref AS event_ref
FROM event
inner join (

SELECT organisation_ref, MAX(event_ref) AS event_ref
FROM event
GROUP BY organisation_ref, event_ref
HAVING (((organisation_ref) Is Not Null)) ) X
On X.organisation_ref = event.organisation_ref and  X.event_ref =  event.event_ref
0
 

Author Comment

by:antonioking
ID: 39737772
Hi,
Thanks for your assistance, the query seems to still return unwanted rows.

organisation_ref      event_date      create_user      event_ref
481570      23/12/2013      658840      61935080
481560      24/12/2013      7363780      61936480
481560      23/12/2013      7363780      61934740
481560      23/12/2013      7363780      61934330
481560      23/12/2013      7363780      61934200
481560      20/12/2013      554690      61931840
481560      20/12/2013      554690      61931800
481550      19/12/2013      7366260      61919410
0
 

Author Closing Comment

by:antonioking
ID: 39737799
had to slightly adapt this query but it worked for me.
Thanks
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now