Sql Tricky Queries

select parent_id who have at least one boy and one girl

Table: parent_id, parent_name, child_id, child_gender

select parent_id

from your_table

group by parent_id

having count(distinct child_gender) = 2

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

1)

SELECT MAX(col) FROM table WHERE col NOT IN (SELECT MAX(col) FROM table);

2)

SELECT MAX( col )

  FROM table

 WHERE col < ( SELECT MAX( col )

                 FROM table )

This will delete duplicate rows, except first row

DELETE FROM Mytable 

WHERE RowID NOT IN (SELECT MIN(RowID) 

                    FROM Mytable 

                    GROUP BY Col1,Col2,Col3)

DELETE LU 

FROM   (SELECT *, 

               Row_number() 

                 OVER ( 

                   partition BY col1, col1, col3 

                   ORDER BY rowid DESC) [Row] 

        FROM   mytable) LU 

WHERE  [row] > 1 

SELECT (SYSDATE) +level-1 each_day FROM DUAL CONNECT BY LEVEL <= 10

0/p:

18-DEC-13

19-DEC-13

20-DEC-13

21-DEC-13

22-DEC-13

23-DEC-13

24-DEC-13

25-DEC-13

26-DEC-13

27-DEC-13