ProgrammingOcean

SELECT Dense_Rank() OVER ( ORDER BY receiptdate ) as Firt_Arrival,locationnumber,receiptdate FROM inventorylocsubdetail Here Dense_Rank() fn would return the rank based on the ReceiptDate. But this would be in sequence even if there are same rank more than one. ( This is the major different between Rank() and Dense_Rank() ) E.g: here rank 4 repeating for three records,though next rank is 5 (4,4,4,5)

SELECT Rank() OVER ( ORDER BY receiptdate ) as Firt_Arrival,locationnumber,receiptdate FROM inventorylocsubdetail Here Rank() fn would return the rank based on the ReceiptDate. But this would be jumbling if there are same rank more than one. E.g: here rank 4 repeating for three records,because of this the next rank is 7 (4,4,4,7)

In oracle SQL *Plus Worksheet you can simply export the query and results to external files rather copy and paste. E.g :If you want to export the all employee details to Text file, Here it is the very simple solution ; Spool c:\Employees_Data.txt; Selec t * From Employees ; Once you run this query,The Query and Results would export to Employees_Data.txt file.

SELECT JobNo,BankName , NoofLeaves,NoofBooks ,bcode,ROW_NUMBER() over (partition by bcode order by bcode) as Rank FROM ChequeDetails WHERE JobNo='161008LN1' and BankName='008'

Hi folks, This is very useful when we use query results with a table. for example here i have written query to get the sum of books from one table then i join that query resultset with another table in which i have sum up. with chequedet( BCode,NoofLeaves,TTLBooks) as ( select BCode,NoofLeaves,sum(NoofBooks) TTLBooks from ChequeDetails where JobNo='xxxxxxx' group by BCode,NoofLeaves ) select a.bcode,b.NoofLeaves, b.TTLBooks,sum(a.noofbooks) from ChequeDetails a ,chequedet b where a.BCode=b.BCode and JobNo='xxxxxxx' group by a.bcode,b.NoofLeaves,b.TTLBooks order by a.bcode,b.NoofLeaves, b.TTLBooks

SELECT row_number() OVER (ORDER BY DeliveryLocationCode) AS Row_No,DeliveryLocationCode, ContactPerson FROM DeliveryLocationMaster

SQL Server/Oracle/MySQL Union query Many time we require results from different tables or different queries. We try one after the other and list the result accordingly. There is a better way to do this by using UNION command or query. We can list records of two different tables by combining two sql queries to one. What is the advantage ? We can as well write the query two times and display one after the other. The point here is we can pick up unique records also. Say we have two tables one is storing name of the football players and other is storing players of baseball team. We want to display a list of players who are member of one of the team at least. Here we can display the name of the players by using UNION command. If John is member of football and baseball team then his name will appear once only. If we want to display all the records then we can use ALL along with the UNION command. Here are two tables with three records each. Please note that there is one common record ...