Hello Everybody,
I face a problem to
SELECT the paticular customer record from 50 records in database whose salary is 15 th heighest with out using 'limit' keyword in mysql or 'rownum' in oracle.
query should be in standard SQL ,which will run in all oracle,mysql,an d in sql server also.
please try............ .....
5 1464
Hello Everybody,
I face a problem to
SELECT the paticular customer record from 50 records in database whose salary is 15 th heighest with out using 'limit' keyword in mysql or 'rownum' in oracle.
query should be in standard SQL ,which will run in all oracle,mysql,an d in sql server also.
please try............ .....
Please post what you have tried for my reference!!
Please post what you have tried for my reference!!
I have tried the following queries
the following query runs in mysql to treat name of the employee of 30 th heighest salary.
SELECT name FROM SALARY_TABLE ORDER BY SALARY DESC LIMIT 29,1
the following query runs in oracle to treat name of the employee of 30 th heighest salary.
SELECT ROWNUM, name FROM SALARY_TABLE ORDER BY SALARY DESC where ROWNUM=30;
But I like to write a common query to run in both. I face this question in some interview table
I have tried the following queries
the following query runs in mysql to treat name of the employee of 30 th heighest salary.
SELECT name FROM SALARY_TABLE ORDER BY SALARY DESC LIMIT 29,1
the following query runs in oracle to treat name of the employee of 30 th heighest salary.
SELECT ROWNUM, name FROM SALARY_TABLE ORDER BY SALARY DESC where ROWNUM=30;
But I like to write a common query to run in both. I face this question in some interview table
try this: -
-
SELECT ename,g.msal FROM (select MIN(salary) msal from emp e where 30 > (SELECT count(*) from emp where salary > e.salary)) g,emp d
-
WHERE d.salary = g.msal
-
-
The above query will give you the enames of all the employees whose salary is 30th highest.
It will return all names if two employees have same salary at 30th position!!
try this: -
-
SELECT ename,g.msal FROM (select MIN(salary) msal from emp e where 30 > (SELECT count(*) from emp where salary > e.salary)) g,emp d
-
WHERE d.salary = g.msal
-
-
The above query will give you the enames of all the employees whose salary is 30th highest.
It will return all names if two employees have same salary at 30th position!!
*************** **********reply *************** *************** **********
thanks a lot for sending this query. this is running fine
*************** **********reply *************** *************** **********
thanks a lot for sending this query. this is running fine
You are Welcome :)
Regards,
Amit
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Maroon |
last post by:
Hi,
I want to write a standard java user defined function, like this
example:-
select db_fun("roll"), roll from student;
**db_fun() will work for all tables of the all databse
here db_fun is the function that i want to write.
The student table is:-
roll name
1 'A'
|
by: Dani |
last post by:
Hi,
I´m no superuser when it comes to MS Access. So I use MS Query to pull
out info from an SQL database to Excel.
I have a tabel containing different "titles" or "colums".
One contains a date. When executing the query. And getting the data to
Excel the date is in the standard date format (YYYY-MM-DD).
I would like to get this data in a...
|
by: Papegoja |
last post by:
Hi,
A week ago I had a question how to get MS Query to return a standard
date to a "YEAR-MONTH" (YYYY-MM) format.
I received a great answer from Jerry Boone!!!
It follows:
SELECT convert(varchar(4),year(INVENTTRANS.DATEFINANCIAL)) + '-' +
convert(varchar(2),month(INVENTTRANS.DATEFINANCIAL)) AS 'dfDate'
|
by: Sam Lazarus |
last post by:
I need to import data from a website, but the text is arranged badly:
Name:John Doe
Title:Grunt
ID:314159
Name:Jane Doe
Title:Queen-Bee
ID:271828
etc...
|
by: Tim Donaldson |
last post by:
Hi,
I wonder if anyone has some ideas on the best way to create this type of
query. I have a table of four columns. The first column field is
called "year" and contains years from 1980 to 2003. I have a standard
set of years which I have to convert all of the year values in the table
into.
For example, the standard set is:
1980,...
| |
by: Ross A. Finlayson |
last post by:
Hi,
I'm scratching together an Access database. The development box is
Office 95, the deployment box Office 2003.
So anyways I am griping about forms and global variables. Say for
example I'm adding a customer. The Customer fields are mostly foreign
keys that refer to primary keys in other tables, left join instead of
junction tables...
|
by: allyn44 |
last post by:
Hello,
I am trying to figure out how to create a sdev field on a report or in
a query that is based on a claculation of 2 fields.
I have these fields:
Number of samples--this is a calcualted field in query (counts samples
grouped by a "week" and "group" fields)
|
by: Nishant Saini |
last post by:
Dear All,
We have a database which contains many tables which have millions of
records. When We attach the database with MS SQL Server 2005 Standard
Edition Server and run some queries (having joins, filters etc.) then
they take very long time to execute while when We execute same queries
on Enterprise Edition then they run 10 times faster...
|
by: martin |
last post by:
Hello,
Today I fetched the standard Access-2003 template "orders management db"
from
http://office.microsoft.com/en-au/templates/TC010185481033.aspx?pid=CT101426031033
In de openingform one sees a column "extended price"
Perhaps I dont understand this too well, but shouldn't this be something
like quantity*unitprice?
|
by: Twanne |
last post by:
Hi,
I'd like to use a standard value in a field. This value should be the lowest value of a table minus 1. The query I wrote for this works and looks like this:
SELECT Min(dkey) AS dkeylock
FROM VetAbsorptie;
Now when I ask for dkeylock with the build function (I select it from the query), access gives me a #NAME? error. Anybody who...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
| |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it. ...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
|
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...
| |