logo
down
shadow

Select max date or null date from same table MySQL


Select max date or null date from same table MySQL

By : henrikdetjen
Date : November 22 2020, 10:48 AM
wish of those help I would to select the max date or null but display twice for the Company Code. , UPDATED
Please try the corrected version:
code :
SELECT 
    MT_EMPLOYEE_CONTRACT.EMPLOYEE_CODE , 
    MT_EMPLOYEE_CONTRACT.COMPANY_CODE, 
    MT_EMPLOYEE_CONTRACT.RESIGNATION_DATE
FROM 
    MT_EMPLOYEE_CONTRACT 
    INNER JOIN 
    (
        SELECT 
            A.EMPLOYEE_CODE , 
            MAX(A.RESIGNATION_DATE) AS MAX_RESIGNATION_DATE
        FROM 
            MT_EMPLOYEE_CONTRACT AS A
        GROUP BY 
            A.EMPLOYEE_CODE
     ) AS B
     ON MT_EMPLOYEE_CONTRACT.EMPLOYEE_CODE = B.EMPLOYEE_CODE 
        AND  MT_EMPLOYEE_CONTRACT.RESIGNATION_DATE = B.MAX_RESIGNATION_DATE;
WHERE
    NOT ISNULL(MT_EMPLOYEE_CONTRACT.RESIGNATION_DATE)

UNION 

SELECT 
    MT_EMPLOYEE_CONTRACT.EMPLOYEE_CODE , 
    MT_EMPLOYEE_CONTRACT.COMPANY_CODE, 
    MT_EMPLOYEE_CONTRACT.RESIGNATION_DATE
FROM 
    MT_EMPLOYEE_CONTRACT 
WHERE
    ISNULL(MT_EMPLOYEE_CONTRACT.RESIGNATION_DATE)


Share : facebook icon twitter icon
MySQL select date range from table where there's only one column for date

MySQL select date range from table where there's only one column for date


By : Blazian Varun
Date : March 29 2020, 07:55 AM
this will help having this record in MySQL database:
code :
SELECT emp_name, team, min(start_date) start_date, end_date
  FROM (
    SELECT t1.emp_name, t1.team, t1.start_date, min(t2.start_date) end_date
      FROM table1 t1
 LEFT JOIN table1 t2
        ON t1.emp_name = t2.emp_name
       AND t1.team != t2.team
       AND t1.start_date < t2.start_date
  GROUP BY t1.emp_name, t1.team, t1.start_date) t3
GROUP BY emp_name, team, end_date
ORDER BY start_date
MySQL select max date or null date

MySQL select max date or null date


By : Utkarsh Deoli
Date : March 29 2020, 07:55 AM
Hope this helps I would to select the max date or null but it shows a error message. ,
Can Anyone find out what is the problem?
code :
MAX(RESIGNATION_DATE IS NULL)= 0
select emp_code, res_date /* first select null emp_code*/
From Table_name
where res_date IS NULL group by emp_code
UNION 
select emp_code, MAX(res_date ) /* select max from non - null emp_code*/
From Table_name
where emp_code NOT IN 
     (select emp_code From Table_name 
      where res_date IS NULL) 
group by emp_code
select DISTINCT emp_code, res_date /* first select null emp_code*/
From Table_name
where res_date IS NULL 
UNION 
select emp_code, MAX(res_date ) /* select max from non - null emp_code*/
From Table_name
where emp_code NOT IN 
     (select emp_code From Table_name 
      where res_date IS NULL) 
group by emp_code 
MySQL select table column 31-12-2014 type varchar as date order by date php

MySQL select table column 31-12-2014 type varchar as date order by date php


By : user2652973
Date : March 29 2020, 07:55 AM
Hope this helps You are on the right track, but before you define the DATE_FORMAT you need to convert your VARCHAR into a date. Further, you need to format the date in a way it can be properly sorted (thus only used by ORDER BY) but display it the way it is stored (if that's what you want):
code :
SELECT product, price, date 
FROM `tableone` 
WHERE `user` = '$usernamefromform' 
ORDER BY DATE_FORMAT(STR_TO_DATE(date, '%d-%m-%Y'), '%Y%m%d') DESC
SELECT product, price, date 
FROM `tableone`
WHERE `user` = '$usernamefromform' AND (date BETWEEN '2014-01-30' AND '2014-12-31')
ORDER BY date DESC
MySql partitioned table - select between date range using PK very slow compared to date IN (...) clause

MySql partitioned table - select between date range using PK very slow compared to date IN (...) clause


By : user2602868
Date : March 29 2020, 07:55 AM
it should still fix some issue Usually it is inefficient to start the PRIMARY KEY with the column(s) of the "partition key". After all, first comes the "partition pruning"; why then filter on the same stuff?
Usually it is inefficient to start any composite index with a column that will be used in a "range" test. It is subtle, but I think this explains the difference you are seeing. With IN (your second query), more fields in the PK can be used, thereby running faster.
code :
WHERE record_date BETWEEN '2019-03-12' AND '2019-03-15'
  AND unit_id = 1148210
  AND enum_key IN (9, 10, 38, 311)

`record_date` date NOT NULL,
PRIMARY KEY (`record_date`,`unit_id`,`enum_key`)

PARTITION BY RANGE (TO_DAYS(record_date))
PRIMARY KEY(unit_id, enum_key, record_date)
MYSQL: select a date from a table when a condition A satisfies; if date is null, then select date from condition B

MYSQL: select a date from a table when a condition A satisfies; if date is null, then select date from condition B


By : Aniruddha Deshpande
Date : March 29 2020, 07:55 AM
help you fix your problem How about ranking rows by DATE_QUAL_CD?
In this example, EFFD exists so its DATE_VALUE (100) is returned:
code :
SQL> with iso_date (date_value, date_qual_cd) as
  2    (select 100, 'EFFD' from dual union all
  3     select 200, 'PRIM' from dual union all
  4     select 300, 'XXXX' from dual
  5    ),
  6  temp as
  7    (select date_value,
  8         date_qual_cd,
  9         rank() over (order by case when date_qual_cd = 'EFFD' then 1
 10                                    when date_qual_cd = 'PRIM' then 2
 11                                    else 3
 12                               end) rn
 13     from iso_date
 14    )
 15  select date_value
 16  from temp
 17  where rn = 1;

DATE_VALUE
----------
       100

SQL>
SQL> with iso_date (date_value, date_qual_cd) as
  2    (select 100, 'ABCD' from dual union all
  3     select 200, 'PRIM' from dual union all
  4     select 300, 'XXXX' from dual
  5    ),
  6  temp as
  7    (select date_value,
  8         date_qual_cd,
  9         rank() over (order by case when date_qual_cd = 'EFFD' then 1
 10                                    when date_qual_cd = 'PRIM' then 2
 11                                    else 3
 12                               end) rn
 13     from iso_date
 14    )
 15  select date_value
 16  from temp
 17  where rn = 1;

DATE_VALUE
----------
       200

SQL>
Related Posts Related Posts :
  • select the records with the highest amount
  • Changing Mysql golbal variable
  • How to create Effective Indexing on MySQL
  • `require': Incorrect MySQL client library version
  • How to save a FirstName of a Person efficiently without duplicate entries
  • Can I store my own sql query by mysql special function and execute stored query later?
  • MySQL comma separated field query
  • Why does the same exact query produce 2 different MySQL explain results?
  • Suppress warning output in bash
  • Doctrine "on update CURRENT_TIMESTAMP" annotation (Symfony2)
  • Designing "Friend" relationships across two mysql tables
  • Importing sql file using phpmyadmin in EasyPHP
  • Easy PHP and mysql ..php cannot see database on local pc (all works on server)
  • MySQL join optimization - bypassing filesort
  • How to Select record with the maximum value in a GROUP BY?
  • MySQL Innodb Full text Proximity Search Gives Horrible Performance
  • MySQL best approach for db normalising, relationships and foreign keys
  • How to update one table based on the count value of another table while matching ids?
  • MAMP Pro 5.1.1-terminates MySQL when quitting
  • SQL - Get date of most votes cast
  • "Fuzzier" search in mysql
  • Tracing mysqld Using SystemTap
  • SELECT + INSERT + Query Cache = MySQL lock up
  • SQL vs. NoSQL for medium complexity search systems
  • SQL Error when creating table
  • Is it possible to write a query to compare rows to other rows in same table?
  • Display all duplicate records based multiple columns
  • summing time column in mysql
  • InnoDB: Cannot open table
  • Why is IN() considered O(logN) operation?
  • Can a deadlock occur on commit?
  • Is there a fast way to do a '%phrase%' search in any DB?
  • Is using an IN over a huge data set a good idea?
  • How can I know if a table is already arranged in ascending order in MySQL?
  • MySQL - Designing database for a simple game
  • Why am I getting "Host '192.168.1.220' is not allowed to connect to this MySQL server"?
  • what type of password hash? possible mysql hash?
  • MySQL - Using a date range vs functions
  • Using IS NULL and COALESCE in OrderBy Doctrine Querybuilder
  • How to merge MySQL columns into one column if there are empty values in the table?
  • Cardinality Confusion
  • Renaming column multiple ids in SELECT
  • Group all from and to messages per user
  • How to handled the null values in WHERE IN condition in mysql
  • Is it okay to update primary key field to shift a record to the top?
  • Stop query through pdo
  • How to check whether particular value in field in MySQL exists
  • Execute a statement for every table in a database
  • insertion 74G data into mysql table cost more then 2 days, how to improve insert performance
  • Where can I see the differents "Hibernate Console Configurations" in Eclipse
  • MySql | relational database vs non relational database in terms of Performance
  • Exporting mysql database using mysqldump including procedures
  • Trigger MySQL not working
  • How do I update values for individual rows quickly?
  • MySQL join query with multiple where condition
  • MySQL stored procedure is not working
  • MySQL - Order By exact match at the end
  • Best solution workaround for needing a mysql event on insert?
  • MySQL select several columns of several tables at the same time without using JOIN
  • Is it more efficient to have a table with one of its columns mostly empty or make a new table and refer to it?
  • shadow
    Privacy Policy - Terms - Contact Us © ourworld-yourmove.org