Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Query to return unique row based on column 1

Posted on 2013-12-24
7
436 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

860 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