What is the simplest SQL Query to find the second largest value?

SELECT MAX(col)
  FROM table
 WHERE col < (SELECT MAX(col)
                FROM table)
SELECT MAX(col) 
  FROM table 
 WHERE col NOT IN (SELECT MAX(col) FROM table);
Posted in SQL.

UNION removes duplicate records (where all columns in the results are the same), UNION ALL does not.

There is a performance hit when using UNION vs UNION ALL, since the database server must do additional work to remove the duplicate rows, but usually you do not want the duplicates (especially when developing reports).

UNION Example:

 SELECT 'foo' AS bar UNION ALL SELECT 'foo' AS bar

Result

+-----+
| bar |
+-----+
| foo |
+-----+
1 row in set (0.00 sec)

UNION ALL example:

 SELECT 'foo' AS bar UNION ALL SELECT 'foo' AS bar

Result

+-----+
| bar |
+-----+
| foo |
| foo |
+-----+
2 rows in set (0.00 sec)
Posted in SQL.
CREATE TABLE ipaddress(id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
                       date VARCHAR(255),
                       ip VARCHAR(255));

INSERT INTO ipaddress(date, ip)
               VALUES('1-1-2012', '195.165.1.2'),
                     ('1-1-2012', '195.165.1.3'),
                     ('12-2-2012', '195.165.1.8');

Total number of Visits should be grouped based on IP Address

SELECT COUNT(ip), MONTHNAME(STR_TO_DATE(date, '%c-%e-%Y')) as period
  FROM ipaddress
 GROUP BY period;

The first Location from second table should be added to the first table Location.

CREATE TABLE projectlist(ProjectId   INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
                         ProjectName VARCHAR(50),
                         Location    VARCHAR(50));
                             
CREATE TABLE LocationList(LocaId INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
                          ProjectId INT,
                          Location    VARCHAR(50));

INSERT INTO projectlist(ProjectName)
                 VALUES('Project A'),
                       ('Project B'),
                       ('Project C'),
                       ('Project D'),
                       ('Project E'),
                       ('Project F'),
                       ('Project G'),
                       ('Project H');
                           
INSERT INTO LocationList(ProjectId, Location)
                  VALUES(1, 'Location A'),
                        (1, 'Location C'),                      
                        (3, 'Location B'),
                        (4, 'Location C'),
                        (5, 'Location D'),
                        (2, 'Location C'),
                        (2, 'Location B'),
                        (2, 'Location A'),
                        (6, 'Location A'),
                        (6, 'Location B'),
                        (7, 'Location B'),
                        (8, 'Location D'),
                        (8, 'Location A');

Query 2

UPDATE projectlist JOIN (SELECT LocationList.ProjectID,
                                LocationList.Location
                           FROM LocationList
                           JOIN (SELECT LocationList.ProjectID,
                                       min(LocaId) minLocaId
                                  FROM LocationList
                                 GROUP BY LocationList.ProjectID) l1
                             ON LocationList.LocaId = l1.minLocaID) l2 ON projectList.ProjectID = l2.ProjectID
   SET ProjectList.Location = l2.Location;
Posted in SQL.
create table fruits(fruit_id int, fruit_name VARCHAR(255));  
create table type(fruit_id int, status VARCHAR(255));

INSERT INTO fruits(fruit_id, fruit_name) 
            values(101, 'Apple'),
                  (102, 'Mango'),
                  (103, 'Lemon'),
                  (104, 'Grape'),
                  (105, 'Orange');

INSERT INTO type(fruit_id, status) 
          values(101, 'Edible'),
                (101, 'Sweet'),
                (102, 'Edible'),
                (103, 'Edible'),
                (103, 'Salty'),
                (103, 'Sour'),
                (104, 'Sour');

When i Use Inner Join as Below the Output would be

To prevent the repetition of fruit name the queries are as follows

SELECT f.fruit_id, f.fruit_name
  FROM fruits f
 WHERE f.fruit_id in(SELECT DISTINCT fruit_id
                       FROM type);

SELECT DISTINCT t.Fruit_id, fruit_name
  FROM fruits f RIGHT OUTER JOIN
       type t ON F.Fruit_id = t.Fruit_id;

SELECT fruits.*
  FROM fruits inner join
       type on type.fruit_id = fruits.fruit_id
  GROUP BY type.fruit_id;
Posted in SQL.

Table Creation Script

CREATE TABLE Customers(Row_Id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
                       Cust_Name VARCHAR(255),
                       Created_Date DATE,
		       Cust_Status TINYINT)

The Rows in the table are as Below

INSERT INTO Customers(Cust_Name, Created_Date, Cust_Status)
               VALUES('Customer A', '20120516', 0),
                     ('Customer B', '20120516', 0),
                     ('Customer C', '20120516', 0),       
                     ('Customer A', '20120517', 1),
                     ('Customer B', '20120517', 0),
  		     ('Customer C', '20120517', 0),
		     ('Customer A', '20120520', 1),
		     ('Customer B', '20120520', 0),
	             ('Customer C', '20120520', 1),
		     ('Customer A', '20120521', 0),
                     ('Customer B', '20120521', 0),
		     ('Customer C', '20120521', 1),                                                               
                     ('Customer A', '20120526', 1),
		     ('Customer B', '20120526', 1),				 	
                     ('Customer C', '20120526', 0),				 	
                     ('Customer A', '20120530', 1),
		     ('Customer B', '20120530', 1),				 		
                     ('Customer C', '20120530', 0);

I want to take the rows which changes their Cust_Status from 0 to 1 only for first time.
When I run the script by giving 20120517 as parameter to where clause it should return Customer A.
When I run the script by giving 20120520 as parameter to where clause it should return Customer C.
When I run the script by giving 20120526 as parameter to where clause it should return Customer B.

SELECT  C.Cust_Name
  FROM  Customers C
 WHERE  C.Created_Date = '20120526' AND
        C.Cust_Status  = 1          AND
        NOT EXISTS (SELECT c2.Cust_Name
  	              FROM Customers c2
      		     WHERE c2.Cust_Name   = C.Cust_Name AND
	                   c2.Cust_Status = 1 AND
	                   c2.Created_Date &lt; C.Created_Date)
Posted in SQL.

Two tables

CREATE TABLE tblEatables (
    `EatId` int UNSIGNED PRIMARY AUTO_INCREMENT,
    `Fruits` varchar(9) NOT NULL
) Engine=InnoDB;

CREATE TABLE tblConfirm_Eatables (
    Eatables_Id INT UNSIGNED,
    Edible_Status INT,
    FOREIGN KEY Eatables_Id REFERENCES tblEatables (EatId)
) Engine=InnoDB;

Rows in Tables

INSERT INTO tblEatables(`EatId`, `Fruits`)
       VALUES(1, 'Apples'),
             (2, 'Oranges'),
             (3, 'Papaya'),
             (4, 'Jackfruit'),
             (5, 'Pineapple'),
             (6, 'Mango');

INSERT INTO tblConfirm_Eatables
    VALUES(1,0),
          (2,1),
          (3,0),
          (4,0);

The Result Should be

  Fruits
  Apple
  Papaya
  Jackfruit
  Pineapple
  Mango

Query1

  SELECT Fruits
    FROM tblEatables AS E LEFT JOIN 
         tblConfirm_Eatables AS CE ON E.EatID = CE.Eatables_ID
   WHERE CE.Edible_Status = 0 OR 
         CE.Edible_Status IS NULL

Query 2

 SELECT e.EatId,e.Fruits
   FROM @tblEatables e LEFT JOIN 
        @tblConfirm_Eatables ce ON e.EatId = ce.Eatbles_Id
  WHERE ce.Edible_Status  = 0 OR ce.Edible_Status IS Null

Query 3

 SELECT fruits 
   FROM tblEatables 
  WHERE EatID  NOT IN (SELECT Eatables_Id 
                         FROM tblConfirm_Eatables 
                        WHERE   Edible_Status = 1)           

I have Table with Columns Name, EmailId, Process, Status Like Below. Now what i would like to have is the emailId and Status of people whoes Status are closed for all the Process – Process1, process2, process 3

Input
 

Output

Query1

  SELECT  fruit_email_id
    FROM  Fruits AS t
   GROUP BY fruit_email_id
  HAVING COUNT( CASE WHEN Status  'Closed' 
                     THEN 1                       
                      END) = 0;

Query2

   SELECT  t.fruit_email_id
     FROM  (SELECT DISTINCT fruit_email_id
              FROM Fruits) AS t
              LEFT JOIN Fruits AS tt ON  tt.fruit_email_id = t.fruit_email_id AND 
                   tt.Status  'Closed'
    WHERE tt.fruit_email_id IS NULL;
Posted in SQL.