CODEWITHSUNDEEP
mysqlsql
Andrew
Andrew

Reputation: 167

SQL query for each ID in table

I have a query for 1 particular customer_id. How can I execute this query for each customer_id in table?

SELECT      *
FROM        table
WHERE       date <= '2015-12-31 23:59:59' AND customer_id = 100
ORDER BY    date DESC
LIMIT       1

Upvotes: 3

Views: 3590

Answers (2)

Rohit Gupta
Rohit Gupta

Reputation: 455

You can use Inner join:

SELECT * FROM YourTable t INNER JOIN
(SELECT * FROM Table) s
ON t.customer_id = s.customer_id
WHERE t.date = '2015-12-31 23:59:59'
ORDER BY date DESC

Upvotes: 0

sagi
sagi

Reputation: 40481

You can use NOT EXISTS():

SELECT * FROM YourTable t
WHERE t.date <= '2015-12-31 23:59:59'
 AND NOT EXISTS(SELECT 1 FROM YourTable s
                WHERE t.customer_id = s.customer_id
                AND t.date < s.date)

This will select only a record after the date filter where NOT EXISTS another record for the same id with a bigger date. Its basically the same as limit 1 for all.

Upvotes: 1

Related Questions