SELECT * FROM Questions WHERE Type = 'Stupid'

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > SELECT * FROM Questions WHERE Type = 'Stupid'

USSR Lives On: Jun 10, 2003; asdf

I have a table that's basically a log of searches that has a userid, the search, and the timestamp as the columns. I'm trying to run a query that gets a users last few unique searches. Let's say I have a table like:

code:

userid   search    timestamp
============================
  1       foo     1234567890
  1       bar     1234567891
  1       you     1234567892
  1       foo     1234567893

If I want the last two unique searches, I would run a query like:

code:

SELECT DISTINCT search FROM searches WHERE userid = 1 ORDER BY timestamp DESC LIMIT 2

What I expect to get is foo and you.
What I actually get is you and bar.

I suspect that the last foo is cut out of the results because the DISTINCT clause matches it with the first foo and eliminates it before the ORDER BY is ever applied. Is there some way to get around this?

# ¿ Dec 8, 2007 05:15

Adbot: ADBOT LOVES YOU

# ¿ May 7, 2024 13:37

USSR Lives On: Jun 10, 2003; asdf

Sorry guys I thought I replied, but apparently not. I seem to have solved it by running this:

code:

SELECT search FROM searches WHERE userid = 1 GROUP BY search ORDER BY MAX(timestamp) DESC LIMIT 2

I'm not sure if it's going to start breaking due to some of the other things you guys have mentioned, but it's working pretty well so far... I'll report back if I notice things breaking. A couple of people have said that it's not working though, so I'm going to do some more thorough testing.

# ¿ Dec 9, 2007 17:20

USSR Lives On: Jun 10, 2003; asdf

I have a fun little query that I wanted to write, and I thought it would be really simple, but turns out that it may actually be impossible to do in one query. I wanted to get your guys' ideas about what may be the best way of doing this.

I have two tables. One is a table of items. The other is a table of attributes, with multiple possible attributes of several attribute types possible for each item. So I have something like this:

code:

 item_id   item_name
---------------------
    1        foo
    2        bar

 item_id     tag_name    tag_type
----------------------------------
   1           red         color
   1          green        color
   1          heavy        weight

Given that there will only be 2-3 types of tags, is it possible to create a query that will return the number of tags of each type that were assigned to each item? For example, is it possible to run a query on the example above and get back that item 1 was assigned a color tag twice and a weight tag once?

I've been trying different group by and count combinations, but at this point I'm ready to give up and just give each tag type its own table. Is there a way of just making this one query or should I just separate the tag types into their own tables?

# ¿ Jan 24, 2008 23:12

USSR Lives On: Jun 10, 2003; asdf

Rumsfoord posted:

SELECT Count(attributes.item_id) AS item_attribute_type_count, attributes.tag_type, attributes.item_id
FROM attributes
GROUP BY attributes.tag_type, attributes.item_id

is that what you mean?

The problem with doing this is that if you join on the items table (which you need to do in order to get the rest of the info about that item), this will return one row for each type of attribute, so each item will be returned several times.

# ¿ Jan 25, 2008 04:17

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > SELECT * FROM Questions WHERE Type = 'Stupid'