CODEWITHSUNDEEP
mysql
mitch2k
mitch2k

Reputation: 526

Select only unique records

I'm trying to select all records in a table, but hide duplicate rows. So if 2 rows are completely the same (except for the auto increment ID of course), only 1 should be shown.

I thought this had to be done with distinct, but it still give me duplicate rows. 

SELECT DISTINCT *
FROM tbllulog
WHERE lulogluserial = $commandlu
ORDER BY `tbllulog`.`tbllulogid` DESC

I also tried this:

SELECT DISTINCT lulogtimestamp,
                lulogmoveemployee,
                lulogsource,
                lulogaction,
                lulogluoutput0status,
                lulogluinput0status
FROM tbllulog
WHERE lulogluserial = $commandlu
ORDER BY `tbllulog`.`tbllulogid` DESC

But this also give me duplicates

Anyone can point me out what i'm missing?

thanks!

Upvotes: 1

Views: 58

Answers (2)

Vignesh Kumar A
Vignesh Kumar A

Reputation: 28413

Try like this

DECLARE @type varchar(50);
DECLARE @num int;
SET @type = '';
SET @num  = 1;

SELECT * FROM
(
SELECT lulogtimestamp,
       lulogmoveemployee,
       lulogsource,
       lulogaction,
       lulogluoutput0status,
       lulogluinput0status
       @num := if(@type = lulogmoveemployee, @num + 1, 1) as row_number,
       @type := lulogmoveemployee As Dummy
FROM tbllulog
WHERE lulogluserial = $commandlu
ORDER BY `tbllulog`.`tbllulogid` DESC
) T WHERE row_number = 1

Upvotes: 1

Daniel Gasser
Daniel Gasser

Reputation: 5143

Use DISTINCT and GROUP BY and add the tbllulogid to the SELECT

 SELECT DISTINCT tbllulogid,
        lulogtimestamp,
        lulogmoveemployee,
        lulogsource,
        lulogaction,
        lulogluoutput0status,
        lulogluinput0status
   FROM tbllulog 
        WHERE lulogluserial = $commandlu 
        GROUP BY `tbllulog`.`tbllulogid` 
        ORDER BY `tbllulog`.`tbllulogid` DESC

Upvotes: 2

Related Questions