filtering out specific ID's

Question

So I have this table where I only want to look at AB.

ID     CODE       COUNT   
102    AB         9
101    AB         8
100    AC         23    //not important!!!!
99     AB         7
98     AB         6
97     AB         5
96     AB         0

I want this table to look like this

ID    NEWID     CODE       COUNT   
102   102       AB         9
101   101       AB         8
99    100       AB         7
98    99        AB         6
97    98        AB         5
96    97        AB         0

How do I get a new ID which counts consecutively where the code is AB?

This is then used for a SELECT query, the initial table should stay intact, so this is not UPDATING the table but just making a fictive NEWID.

Do you want the `NEWID` field for a `SELECT` query? Or do you want the table itself to be modified? — impl, Jan 26 '13 at 09:39

score 1 · Accepted Answer · answered Jan 26 '13 at 10:00

You can use a user variable to create a counter to accomplish this (in just one query!):

SELECT t.ID, @NEWID := COALESCE(@NEWID - 1, t.ID) AS NEWID, t.CODE, t.COUNT
FROM
    (SELECT ID, CODE, COUNT FROM some_table WHERE CODE = 'AB' ORDER BY ID DESC) t,
    (SELECT @NEWID := NULL) _uv;

mysql> SELECT t.ID, @NEWID := COALESCE(@NEWID - 1, t.ID) AS NEWID, t.CODE, t.COUNT
    -> FROM
    ->     (SELECT ID, CODE, COUNT FROM some_table WHERE CODE = 'AB' ORDER BY I
D DESC) t,                                                                        
    ->     (SELECT @NEWID := NULL) _uv;
+------+-------+------+-------+
| ID   | NEWID | CODE | COUNT |
+------+-------+------+-------+
|  102 |   102 | AB   |     9 |
|  101 |   101 | AB   |     8 |
|   99 |   100 | AB   |     7 |
|   98 |    99 | AB   |     6 |
|   97 |    98 | AB   |     5 |
|   96 |    97 | AB   |     0 |
+------+-------+------+-------+
6 rows in set (0.03 sec)

Edit: Here's an SQL Fiddle for it. (What a cool tool. I had no idea this existed!)

Best and easiest! Thanks alot! – Lazykiddy Jan 26 '13 at 10:10 — Lazykiddy, Jan 26 '13 at 10:10

score 0 · Answer 2 · answered Jan 26 '13 at 09:47

0

You can use this:

SELECT
   Id, @newid := @newid - 1 as NEWID, CODE, CNT
FROM Tbl,
(SELECT @newid := MAX(ID) + 1 FROM Tbl) A
WHERE CODE != 'AC'
ORDER BY NEWID DESC

SQL FIDDLE

answered Jan 26 '13 at 09:47

Hamlet Hakobyan

32,965
6
52
68

`ORDER BY NEWID` sounds like a really bad idea, as the `NEWID` column is only computed later on. If you really want to sort by that column, you should probably use a subquery. But ordering by `ID` is enugh here, I believe. – MvG Jan 26 '13 at 09:51
@MvG `NEWID` computed after ordering result set? This is something new. `ORDER BY clause` occurs after result st is generated. – Hamlet Hakobyan Jan 26 '13 at 09:54
http://stackoverflow.com/a/12729068/1468366 lists `@variable` resolution *after* `ORDER BY`. And as you can `ORDER BY` any other column and then generate row numbers for that order using a user variable, this order appears correct to me. Quoting a quotation from said answer: “In a SELECT statement, each select expression is evaluated only when sent to the client.” Which again supports `ORDER BY` after `@variable`. – MvG Jan 26 '13 at 10:59
I guess my core point is not that `ORDER BY NEWID` won't work, because according to your fiddle it does. My point is rather that it might cease to work as intended in some future version of MySQL, and the clause is utterly pointless: as the numbers are generated in decreasing order, ordering them again serves no purpose at all. Not sorting by old IDs means the assignment between old and new IDs is pretty much arbitrary, and might in theory change from one execution of the query to the next. – MvG Jan 26 '13 at 11:13
@MvG OK, for first i don't see any link to documentation. “In a SELECT statement, each select expression is evaluated only when sent to the client.” What does it mean? How can part of select list will be sent to client? Then can you explain result of this simple query `SELECT fld1 + fld2 AS sumfld FROM tbl ORDER BY sumfld` ? If expression will be evaluated after `ORDER BY clause` it wont be compiled or at minimum wont be run. – Hamlet Hakobyan Jan 26 '13 at 11:37

score 0 · Answer 3 · answered Jan 26 '13 at 09:48

0

Try this:

SELECT MIN(ID) - 1
FROM tab
WHERE CODE = 'AB'
INTO @i;

SELECT tab.ID, (@i := @i + 1) AS NEWID, tab.COUNT
FROM tab
WHERE tab.CODE = 'AB'
ORDER BY tab.ID;

See also http://sqlfiddle.com/#!2/2b52e2/1

answered Jan 26 '13 at 09:48

MvG

57,380
22
148
276

filtering out specific ID's

3 Answers3