Jump to content

MySQL Select less than and other duplicated records


Recommended Posts

Hi

Here is example:of my question.

 

id-----date------ number

1 2012-05-01 100

2 2012-05-01 101

3 2012-05-01 102

4 2012-05-02 103

5 2012-05-03 104

6 2012-05-04 105

7 2012-05-04 101

8 2012-05-04 102

9 2012-05-05 106

 

First, I want to select all records <= 2012-05-03

So, the result will look like below:

 

id------date------ number

1 2012-05-01 100

2 2012-05-01 101

3 2012-05-01 102

4 2012-05-02 103

5 2012-05-03 104

 

But, now, i also want to include the records below. Because the number 101 and 102 also exist in the query above.

 

id------date------ number

7 2012-05-04 101

8 2012-05-04 102

 

 

So, The final result that i want is:

 

id------date------ number

1 2012-05-01 100

2 2012-05-01 101

3 2012-05-01 102

4 2012-05-02 103

5 2012-05-03 104

7 2012-05-04 101

8 2012-05-04 102

 

Could anyone please help how to write the sql query? There are about 100 thousand records in the database table.

 

Hope you guys understand what i means, thanks in advance.

SELECT *
FROM table
WHERE number IN
(SELECT number
 FROM table
 WHERE date <= '2012-05-03')

 

Tried and it will freeze my PC! If without the subquery, it's fast, but if add in the sub-query, it will freeze my PC There are around 100K of records. Just for info only, there are indexing for the table

 

Anyway to solve this?

 

Thanks.

Edited by peterjc

Try this

 

SELECT t.id, t.date, t.number
FROM tablename as t
   WHERE date <= '2012-05-03'
UNION
SELECT t.id, t.date, t.number
FROM tablename as t
INNER JOIN
   (
   SELECT DISTINCT number FROM tablename
   WHERE date <= '2012-05-03'
   ) as X USING (number)

 

I ran a similar query on one of my tables with 182680 records. Time taken 0.8 seconds.

Try this

 

SELECT t.id, t.date, t.number
FROM tablename as t
WHERE date <= '2012-05-03'
UNION
SELECT t.id, t.date, t.number
FROM tablename as t
INNER JOIN
(
SELECT DISTINCT number FROM tablename
WHERE date <= '2012-05-03'
) as X USING (number)

 

I ran a similar query on one of my tables with 182680 records. Time taken 0.8 seconds.

This results duplicates.

we need to exclude the records which are returned from first query by adding where clause in second query in the union .i.e " where t.date > '2012-05-03' . I still feel like there can be more optimal way to do this by making union as subquery.

Try this

 

SELECT t.id, t.date, t.number
FROM tablename as t
WHERE date <= '2012-05-03'
UNION
SELECT t.id, t.date, t.number
FROM tablename as t
INNER JOIN
(
SELECT DISTINCT number FROM tablename
WHERE date <= '2012-05-03'
) as X USING (number)

 

I ran a similar query on one of my tables with 182680 records. Time taken 0.8 seconds.

 

Tried, and it work.

 

but could i know why need to use UNION? because i tried without union, it will return the same result.

 

SELECT t.id, t.date, t.number
FROM tablename as t
INNER JOIN
       (
       SELECT DISTINCT number FROM tablename
       WHERE date <= '2012-05-03'
       ) as X USING (number)

 

Thanks.

You're right.

 

I set out getting the ones less than the date then finding the rest. It is a bit "belt and braces" as the second bit does indeed find those from the first query also, suppressing duplicates.

 

BTW, how was the execution time? Any freezing?

You're right.

 

I set out getting the ones less than the date then finding the rest. It is a bit "belt and braces" as the second bit does indeed find those from the first query also, suppressing duplicates.

 

BTW, how was the execution time? Any freezing?

 

No freezing. :happy-04: Forgot the execution time, but i think less than 1 sec.

 

By the way, regarding the sql

 

SELECT *
FROM table
WHERE number IN
(SELECT number
        FROM table
        WHERE date <= '2012-05-03')

 

I think this sql is correct also right? why it is so slow? Is it because of using the "IN" clause that cause mysql to compare with too many records?

 

Thanks.

If you run an EXPLAIN on that query it should tell you that it is using a DEPENDENT SUBQUERY.

 

Those get called for each row in the database and should definitely be avoided on large tables

If you run an EXPLAIN on that query it should tell you that it is using a DEPENDENT SUBQUERY.

 

Those get called for each row in the database and should definitely be avoided on large tables

 

Ok Barand. By the way, after i got the value and processed in php,

 

NOw, i need to delete or remove all those records.

 

I am using the sql like below, it is a bit slow, any suggestion to improve it?

DELETE FROM table WHERE idtable IN (
SELECT * 
FROM (
SELECT idtable
FROM table AS t
INNER JOIN (
SELECT DISTINCT filename
FROM table
WHERE takendate <=  '2012-05-03'
) AS X
USING ( filename )
) AS p
)

 

 

Thanks in advance.

try same way as the select but with multi-table delete syntax

 

DELETE tablename
FROM tablename
INNER JOIN
(
SELECT DISTINCT number FROM tablename
WHERE date <= '2012-05-03'
) as X USING (number)

Edited by Barand
This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.