Query to return unique row based on column 1

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?
antoniokingAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Pratima PharandeConnect With a Mentor Commented:
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
 
Pratima PharandeCommented:
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
 
antoniokingAuthor Commented:
Thanks, but I want to include the event_date and create_user columns.

Regards
0
A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

 
rshqCommented:
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
 
antoniokingAuthor Commented:
I'm using Microsoft Access 2010, will this work in Access?
0
 
antoniokingAuthor Commented:
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
 
antoniokingAuthor Commented:
had to slightly adapt this query but it worked for me.
Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.