SQL Server Performance

Subtracting to distinct rows

Discussion in 'ALL SQL SERVER QUESTIONS' started by sqlJedi, Oct 22, 2012.

  1. sqlJedi New Member

    I'm having trouble subtracting the following row....

    select lifetime_meters from exchanges ex, lto_exchanges lto where
    lto.lt_exchanges_fk=ex.exchanges_pk AND media_stats_fk is not null order by exchanges_pk DESC limit 1;

    from the following row.......

    select ex.exchanges_pk, ex_drives_fk, ex.lifetime_meters from exchanges ex, lto_exchanges lto where ex.exchanges_pk=lto.lt_exchanges_fk AND ex.ex_drives_fk=93 AND alert_sent = 1 order by ex.exchanges_pk DESC limit 1;

    The following SQL statement (a combination of the 2 above) is not giving the correct answer for the resulting lifetime_meters column even though the 2 SQL statements above do give the correct answers.....

    select ex.exchanges_pk, ex_drives_fk, ex.lifetime_meters, (select (ex.lifetime_meters - ex2.lifetime_meters) from exchanges ex2, lto_exchanges lto where lto.lt_exchanges_fk=ex2.exchanges_pk AND ex2.media_stats_fk is not null AND ex.ex_drives_fk=ex2.ex_drives_fk order by ex2.exchanges_pk DESC limit 1) as meters_since_last_clean from exchanges ex, lto_exchanges lto where ex.exchanges_pk=lto.lt_exchanges_fk AND ex.ex_drives_fk=93 AND alert_sent = 1 order by ex.exchanges_pk DESC limit 1;

    Help?
  2. Shehap MVP, MCTS, MCITP SQL Server

    First , welcome to Forums....

    For such T-SQL queries having shared tables between 2 different select statements, it is much preferable to use CaseWhen Clause as follows :

    select ex.exchanges_pk, ex_drives_fk, ex.lifetime_meters ,

    (CASEWHEN ex.ex_drives_fk=93 AND alert_sent = 1 THEN 1 ENDAS Sum1 )-

    (casewhen media_stats_fk isnotnull then lifetime_meters endas sum2)

    from exchanges ex innerjoin lto_exchanges lto on ex.exchanges_pk=lto.lt_exchanges_fk

    orderby ex.exchanges_pk DESC limit 1

    Despite of that , CaseWhen Clause can speed up significantly queries performance for some cases like aggregative queries , Union queries ..etc and you can read more about this regard at http://www.sqlserver-performance-tu.../12926867-t-sql-performance-optimization-2-6-

    Kindly work out and let me know your feedback
  3. sqlJedi New Member

    Thanks. I tried to make it work. Unfortunately, the 2 rows can not be specifically retrieved from all the rest of the rows without sorting and taking the last 1. The 2 queries take the last entry based on 2 different search criterion. Hence the "order by" and "DESC limit 1" operators. Also, I'm using MySql which I've had some difficulty getting the "end as" to work. Maybe I'm missing something.

Share This Page