CODEWITHSUNDEEP
mysqlsql
Norman
Norman

Reputation: 6365

selecting records less than current id when the value of another column is supplied

How can I select records less than the id of the hash values supplied? Is this even possible? I get the hashKey values from php, and based on that, need to select records that are less than the id of the rows containing that hashKey.

In pseudoCode:

@thisHash = '08d448b86a3b2f7d05a721ef18a8f02f';
select id from myTable where id < {id belonging to @thisHash};

Table Data

"id"    "country"   "hashKey"
"1"     "US"        "319f64ab2c62ee50cc9571f22e3f167b"
"2"     "US"        "9a24e339723632c1f8d4ceb82b6aa098"
"3"     "US"        "021109a71c26e252f75a69905da79d27"
"4"     "US"        "b5f7718d108c9fbc3ad546832d3632d3"
"5"     "SE"        "382155ea04502c976fccbab35dcce290"
"6"     "SE"        "79c1ff6bac2c3c36495ee9c08fc8c211"
"7"     "US"        "21e2eb330732e6d74fc19e260dd5f26f"
"8"     "US"        "e6ef5608014f4e9b697a217010c801a6"
"9"     "SE"        "a53b3d20ada68715b07d09cde2d1e7ba"
"10"    "US"        "08d448b86a3b2f7d05a721ef18a8f02f"
"11"    "US"        "a9ec1a4ce8784249a4ae1c400cc75c8f"
"12"    "SE"        "cedfb21356dd656fe2fd9b2442342b10"
"13"    "US"        "26314a78584de4efcb2b3b4069ba2c43"
"14"    "US"        "b180e756567fb9896e77ee05da9d9b14"

Upvotes: 0

Views: 1340

Answers (2)

peterm
peterm

Reputation: 92785

Use JOIN

SELECT t1.id
  FROM table1 t1 JOIN table1 t2
    ON t2.hashKey = '08d448b86a3b2f7d05a721ef18a8f02f'
   AND t1.id < t2.id

Note: depending on the version of MySQL join-based queries have a better chance to be properly optimized.

Output:

| ID |
|----|
|  1 |
|  2 |
|  3 |
|  4 |
|  5 |
|  6 |
|  7 |
|  8 |
|  9 |

Here is SQLFiddle demo

Upvotes: 1

heretolearn
heretolearn

Reputation: 6545

You could try something like this :

select id from
 myTable where id < (
   select id 
   from myTable 
   where hashkey = '@thisHash'
   );

Upvotes: 1

Related Questions