locked
SQL - how to get minimum from a set of dates RRS feed

  • Question

  • Hi Support,

    I have attached the two tables

    id , Date time  table is the one i'm trying to filter and get the minimum date. 

    ID , member type is the table which will get linked to the above table ( its separate filter which i apply )

    Table Info

    Following is the query i'm using ;

     
    SELECT  nl.id,min(nl.date_time) aS startdate
    			FROM name_log nl INNER JOIN name n ON n.id = nl.ID
    			WHERE nl.LOG_TEXT LIKE 'Name.MEMBER_TYPE:%' 
    				AND ((nl.log_text LIKE '%-> NAT')
    				OR (nl.log_text LIKE '%-> AT')
    				OR (nl.log_text LIKE '%-> NPT')
    				OR (nl.log_text LIKE '%-> PT'))
    			GROUP BY 	nl.id,nl.date_time
    			ORDER BY nl.id,min(nl.date_time)

    The query result i'm getting does not filter the minimum date at present, it shows both records.

    id         date time
    10019 2012-07-27 14:12:26.000
    10019 2013-08-27 09:55:46.000

    Result i'm expecting is :

    10019 2012-07-27 14:12:26.000

    Thank you.

    Las



    LW

    Wednesday, April 29, 2020 11:50 PM

Answers

  • SELECT  nl.id,min(nl.date_time) aS startdate
    			FROM name_log nl INNER JOIN name n ON n.id = nl.ID
    			WHERE nl.LOG_TEXT LIKE 'Name.MEMBER_TYPE:%' 
    				AND ((nl.log_text LIKE '%-> NAT')
    				OR (nl.log_text LIKE '%-> AT')
    				OR (nl.log_text LIKE '%-> NPT')
    				OR (nl.log_text LIKE '%-> PT'))
    			GROUP BY 	nl.id 
    			ORDER BY nl.id,min(nl.date_time)

    • Marked as answer by lasiw Thursday, April 30, 2020 12:05 AM
    Thursday, April 30, 2020 12:01 AM

All replies

  • SELECT  nl.id,min(nl.date_time) aS startdate
    			FROM name_log nl INNER JOIN name n ON n.id = nl.ID
    			WHERE nl.LOG_TEXT LIKE 'Name.MEMBER_TYPE:%' 
    				AND ((nl.log_text LIKE '%-> NAT')
    				OR (nl.log_text LIKE '%-> AT')
    				OR (nl.log_text LIKE '%-> NPT')
    				OR (nl.log_text LIKE '%-> PT'))
    			GROUP BY 	nl.id 
    			ORDER BY nl.id,min(nl.date_time)

    • Marked as answer by lasiw Thursday, April 30, 2020 12:05 AM
    Thursday, April 30, 2020 12:01 AM
  • >> I have attached the two tables <<

    No you have not posted or attached to tables. We have no DDL no idea what the key is or anything else. Are you always this rude to people when you want them to help you?

    Apparently you are so new to RDBMS that you believe there is a thing called a "id"; and RDBMS as well as formal logic to be is to be something in particular so we need "<something>_id".

    I want you to go back and actually follow the forum rules before I'm going to answer you. I'm getting sick and tired after 30+ years of doing this for people that I would kick out of my classes when I was teaching college.

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Thursday, April 30, 2020 7:51 PM