CODEWITHSUNDEEP
mysqlsorting
tihe
tihe

Reputation: 2472

sort on two columns

I have a table with site visits with the following columns:

timestamp
host
url

I would like to see the last few (say 200) visits but at the same time group visits of the same host together (to see the sequence of pages he/she visits).

If I do:

... SORT BY timestamp DESC, host

I get hosts mixed up if several visitors are online at the same time

If I do:

... SORT BY host, timestamp DESC

I do not get the latest visits (just the latest visits from each host)

I would like to see e.g.

9:01 | a.com | /index
9:05 | a.com | /interesting
9:07 | a.com | /contact
9:02 | b.com | /index
9:03 | b.com | /product-a
9:08 | b.com | /thanks-for-buying

Is that possible in Mysql or should I further process the result in php?

Upvotes: 2

Views: 108

Answers (5)

tihe
tihe

Reputation: 2472

The answers above pointed my in the right direction, thanks. This one works. The LIMIT 1000 is to reduce the query from almost 3 minutes to 7 seconds (40000 records).

SELECT v1.stamp, v1.host FROM visits AS v1 
   LEFT JOIN (SELECT MIN(date) AS firstVisit, host
              FROM visitor AS v2 
              GROUP BY v2.host
              ORDER BY firstVisit DESC
              LIMIT 1000) AS j
       ON v1.host=j.host 
       ORDER BY firstVisit DESC, v1.host, v1.date DESC
       LIMIT 100;

Upvotes: 0

newman
newman

Reputation: 2719

I think it is not possible make normal in one query. You can try this:

SELECT * 
FROM table AS t1 
WHERE t1.timestamp_col >= (SELECT MIN(tstamp) 
    FROM (SELECT t2.timestamp_col 
        FROM tables AS t2 
        WHERE t2.host = t1.host 
        ORDER BY timestamp_col DESC 
        LIMIT 200
    )
)

I don't test this crazy code... But I hope it give you way :)

Upvotes: 0

Ja͢ck
Ja͢ck

Reputation: 173562

You can't do ORDER BY followed by LIMIT and then another ORDER BY, so you have to use a sub query:

SELECT * FROM (SELECT * 
    FROM visits
    ORDER BY timestamp DESC
    LIMIT 200
) visits2
ORDER BY host

Upvotes: 1

Leon Armstrong
Leon Armstrong

Reputation: 1303

You have to limit the result if you want the last 200 

  SORT BY timestamp DESC, host LIMIT 0, 200

And if you dont the same visitor to show up again , use 

SELECT DISTINCT visitor FROM ....

Upvotes: 0

Sashi Kant
Sashi Kant

Reputation: 13465

Try this::

Select *, MAX(timestamp_col) as lastVisit from site_table

GROUP BY HOSTS order by lastVisit desc

Upvotes: 1

Related Questions