Geek Answers Handbook

Want to remove duplicate record based only on column value

http://sqlfiddle.com/#!9/ea4d2/1

Here is the SQLfiddle to look at. storepkid 16 is repeated 2 times. But I want it only once. I tried DISTINCT, but it eliminated the whole repeating line so that it would not work in this case. What changes need to be made to the request in order to get the correct result? Please, help.

NOTE: I forgot to mention in the initial question that I also tried GROUP BY, but it gave incorrect data as a result of a set of records. I tried using GROUP BY in the following ways.

http://sqlfiddle.com/#!9/ea4d2/20

http://sqlfiddle.com/#!9/ea4d2/17

NOTE2: @Gabriel Valdez Timbol, I want to get the result as follows. A duplicate storepkid line should be removed.

| Storepkid | selldate | +---------------+-----------------------+ | 19 | August, 25 2015 10:00:00 | | 12 | August, 24 2015 19:00:00 | | 16 | August, 24 2015 16:00:00 | | 15 | August, 23 2015 13:00:00 | | 17 | August, 21 2015 10:00:00 |
+5
source share
3 answers

You can use the max function. Use the following query

 SELECT p.storepkid, max(p.selldate) AS recentselldate FROM (SELECT storepkid, purchasedatetime AS selldate FROM t_product_purchase UNION ALL SELECT storepkid, starttime AS selldate FROM t_service_purchase UNION ALL SELECT storepkid, selldatetime AS selldatetime FROM t_coupon_purchase ) p GROUP BY storepkid order by max(p.selldate) DESC LIMIT 0,5

OUTPUT:

 | Storepkid | selldate | +---------------------------------------+ | 19 | August, 25 2015 10:00:00 | | 12 | August, 24 2015 19:00:00 | | 16 | August, 24 2015 16:00:00 | | 15 | August, 23 2015 13:00:00 | | 14 | August, 21 2015 13:15:00 |

Check out DEMO HERE

+2
source

Here is the correct code, I tried it on your Fiddle fiddle

I also ran into a similar problem ---> Join two tables

  SELECT p.storepkid, p.selldate AS recentselldate FROM (SELECT storepkid, purchasedatetime AS selldate FROM t_product_purchase UNION ALL SELECT storepkid, starttime AS selldate FROM t_service_purchase UNION ALL SELECT storepkid, selldatetime AS selldatetime FROM t_coupon_purchase) p GROUP BY p.storepkid ORDER BY recentselldate DESC LIMIT 0,5
+1
source

Just add a group to the field before the order like this

 SELECT p.storepkid, p.selldate AS recentselldate FROM (SELECT storepkid, purchasedatetime AS selldate FROM t_product_purchase UNION ALL SELECT storepkid, starttime AS selldate FROM t_service_purchase UNION ALL SELECT storepkid, selldatetime AS selldatetime FROM t_coupon_purchase) p GROUP BY storepkid ORDER BY recentselldate DESC LIMIT 0,5
0
source

More articles:


All Articles