Hi,
I have a simple query,
select emp_group,min(salary) from employees group by emp_group.
now I get the results.
Is there a way to get the ID(Primary key Column) value for which the
query returned minimum salary.
Thanks
Kiranselect e.ID,e.emp_group,e.salary
from employees e
inner join (
select emp_group,min(salary)
from employees
group by emp_group) as X(emp_group,minsalary)
on e.emp_group=X.emp_group and e.salary=X.minsalary
Note that you may get multiple rows back for
a particular minimum salary in a group.|||Your result could be not unique as you are only getting the min salary
of a specific emp_group (which I assume is anattribute for
allemployees, so it could be that more than one employee has a specific
emp_group). Therefore you can only get the set of employees (could be
one or more) who have the min salary.
SELECT <columnlist>
FROM employees
WHERE salary IN --OR "="
(
select emp_group,min(salary) from employees group by emp_group
)
HTH, Jens Suessmeyer.|||Hi Kiran
you'll need to join the MIN query to the same table it's based on.
See the thread here:
http://groups.google.com/group/micr...b0e4da5f8f24d99
cheers
Seb|||markc600@.hotmail.com wrote:
> select e.ID,e.emp_group,e.salary
> from employees e
> inner join (
> select emp_group,min(salary)
> from employees
> group by emp_group) as X(emp_group,minsalary)
> on e.emp_group=X.emp_group and e.salary=X.minsalary
> Note that you may get multiple rows back for
> a particular minimum salary in a group.
>
Thanks a lot, that's what I was looking for.
what if it returns multiple rows of min sal in a group and I want the top 1.
Thanks
Kiran|||top 1 based on what criteria?|||sebt wrote:
> Hi Kiran
> you'll need to join the MIN query to the same table it's based on.
> See the thread here:
> http://groups.google.com/group/micr...b0e4da5f8f24d99
> cheers
> Seb
>
Thanks|||markc600@.hotmail.com wrote:
> top 1 based on what criteria?
>
if a group has same min. slaray with two rows, I need the top 1.
based on e.ID
Thanks
Kiran|||select min(e.ID) as ID,e.emp_group,e.salary
from employees e
inner join (
select emp_group,min(salary)
from employees
group by emp_group) as X(emp_group,minsalary)
on e.emp_group=X.emp_group and e.salary=X.minsalary
group by e.emp_group,e.salary|||markc600@.hotmail.com wrote:
> select min(e.ID) as ID,e.emp_group,e.salary
> from employees e
> inner join (
> select emp_group,min(salary)
> from employees
> group by emp_group) as X(emp_group,minsalary)
> on e.emp_group=X.emp_group and e.salary=X.minsalary
> group by e.emp_group,e.salary
>
Thanks a lot Mark
Showing posts with label primary. Show all posts
Showing posts with label primary. Show all posts
Friday, March 9, 2012
Friday, February 24, 2012
Get Record Number
I have a table containing 1000 records. I do have a primary key associated with the table. But I want to get the Record Number of each record. Is there a way to get it. That means for first record it should be 1 and so on and so forth.
That depends on wheter you are using SQL Server 2005 or any version below. SQL 2k5 introduced the ROWNUMBER() function which will let your create a rownumber according to some rules (only a new number if a grouping changes, etc.) if you are using SQL2k or bwlo you will have to go another way. What are you currently using ?
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
|||Currently using SQL-2000|||There is no straight-forward solution in SQL 2000.
Basically, you can create a temp table with an identity column (record number) and insert each row of your table into it. After that, select all the rows in the temp table out with the record number.
You can also only insert the PK column into the temp table and select using a join of the temp table and the original table.
|||I tried to get thru this problem and found out a way to do it. the query specified below can solve my purpose:
SELECT emp_id, lname, fname, job_id, (SELECT COUNT(*) FROM employee e2 WHERE e2.lname <= e.lname) AS rownumber
FROM employee e
ORDER BY lname
But again there is a catch. This particular query will work only in case I have a unique field (like lname in this case). Now I again to resolve this thing by assigning a new unique ID to each row with the use of function NEWID(). But when I am trying to use NEWID() instead of lname it is not working. I tried as
select newid() NN, e.*, (select count(*) from (select newid() NID,* from employee) e2 where e2.NID <= e.NN ) as rownumber
from employee e
it gives me an error "Invalid column name 'NN' " Can anyone help me out in sorting this error and using the NEWID() as a unique field. I do not wish to use temp table.
|||I tried to get thru this problem and found out a way to do it. the query specified below can solve my purpose:
SELECT emp_id, lname, fname, job_id, (SELECT COUNT(*) FROM employee e2 WHERE e2.lname <= e.lname) AS rownumber
FROM employee e
ORDER BY lname
But again there is a catch. This particular query will work only in case I have a unique field (like lname in this case). Now I again to resolve this thing by assigning a new unique ID to each row with the use of function NEWID(). But when I am trying to use NEWID() instead of lname it is not working. I tried as
select newid() NN, e.*, (select count(*) from (select newid() NID,* from employee) e2 where e2.NID <= e.NN ) as rownumber
from employee e
it gives me an error "Invalid column name 'NN' " Can anyone help me out in sorting this error and using the NEWID() as a unique field. I do not wish to use temp table.
get primary key of last inserted record
Ok I know this might not be the most accurate place to post this but I know someone here has an answer for me on it.
I need to get the product_ID of the new record that is created by this insert statement
INSERT
INTO products
( class_ID,category_ID,product_name,product_desc,product_image,product_dimension,product_o1,product_o2,product_o3,product_ac,product_ph,product_photo )
SELECT class_ID,category_ID,product_name,product_desc,product_image,product_dimension,product_o1,product_o2,product_o3,product_ac,product_ph,product_photo
FROM products
WHERE product_ID = @.productID
The answer you seek is here:
http://www.mikesdotnetting.com/Article.aspx?ArticleID=54
Get Primary Key from another Table
Hello everyone,
I've just tried realizing following project:
Currently i have separate Tables with separate Primary Keys (ascending separate Number regions).
Now I want to do the following:
Table - NewID
Field - ID - Identity
Field - IDproc - Sub-Data Number
Table - Data1 (IDProc=1)
Field - ID - Primary Key, generated NEW from NEWiD
Now great would be if I could add a new Recordset in Data1 and I could generate a New ID in 'NewID' trough a Trigger and Set it on the inserted Data, but that doesn't seem possible.
I was able to do it with the following trigger:
CREATE TRIGGER [getNewID] ON [dbo].[Data1]
FOR INSERT
AS
DECLARE @.newID int
INSERT INTO NewID (IDProc) VALUES(1)
SELECT @.newID=(SELECT TOP 1 id FROM NewID WHERE IDProc=1 ORDER BY ID DESC)
UPDATE Data1 SET ID=@.newID WHERE ID IS NULL
But as I can imagine this would get some problems if two users insert a Recordset at the same Time - right?
:mad:
Is there somehow the possiblity to 'SET INSERTED.ID = @.NewID' ??, because it's the only real ID I have in this table (beneath a TimeStamp wich I also couldn't use).
Thanks for any Help!!!First off, if you are using the NewID() function, you will be generating Globally Unique Identifiers, not sequential numbers, so your ORDER BY ID DESC logic won't work.
Are you trying to get the most recent ID inserted? Try creating your ID as a variable within the trigger and then inserting it into both your tables. The NewID() function works well for this.
blindman|||Originally posted by blindman
First off, if you are using the NewID() function, you will be generating Globally Unique Identifiers, not sequential numbers, so your ORDER BY ID DESC logic won't work.
Are you trying to get the most recent ID inserted? Try creating your ID as a variable within the trigger and then inserting it into both your tables. The NewID() function works well for this.
blindman
Hi Blindman
NewID was just a variable for my thoughts, i don't use the function NewID - this is just the tablename and var-name of the trigger...
The idea is to have a main table with all the ID's and sub-data information so I can have a table Data1 Data2 and Data3 wich dont get themselves into the way with their ID's.|||If you are trying to keep IDs from being duplicated within or between data tables, then you should consider using the uniqueidentifier datatype and the NewID function. Its easy to use and will always give you a unique ID. I think it might solve your problem.
blindman|||Originally posted by blindman
If you are trying to keep IDs from being duplicated within or between data tables, then you should consider using the uniqueidentifier datatype and the NewID function. Its easy to use and will always give you a unique ID. I think it might solve your problem.
blindman
Wow, i've just took a look at this NewID function, that's a pretty big thing :-).
I would be pretty happy if i could handle the ID with numbers, not that a customer has to spell me those 20 chars :), do you know any way I could do this trough triggers?
Or is there a possibility to convert the NewID-var to some kind of number?|||NewID produces a standard UniqueIdentifier data type which can be implicitily converted to a char(36) or nchar(36) data type. It's good practice to make your IDs invisible to Users anyway and keep them for internal data processing.
blindman
I've just tried realizing following project:
Currently i have separate Tables with separate Primary Keys (ascending separate Number regions).
Now I want to do the following:
Table - NewID
Field - ID - Identity
Field - IDproc - Sub-Data Number
Table - Data1 (IDProc=1)
Field - ID - Primary Key, generated NEW from NEWiD
Now great would be if I could add a new Recordset in Data1 and I could generate a New ID in 'NewID' trough a Trigger and Set it on the inserted Data, but that doesn't seem possible.
I was able to do it with the following trigger:
CREATE TRIGGER [getNewID] ON [dbo].[Data1]
FOR INSERT
AS
DECLARE @.newID int
INSERT INTO NewID (IDProc) VALUES(1)
SELECT @.newID=(SELECT TOP 1 id FROM NewID WHERE IDProc=1 ORDER BY ID DESC)
UPDATE Data1 SET ID=@.newID WHERE ID IS NULL
But as I can imagine this would get some problems if two users insert a Recordset at the same Time - right?
:mad:
Is there somehow the possiblity to 'SET INSERTED.ID = @.NewID' ??, because it's the only real ID I have in this table (beneath a TimeStamp wich I also couldn't use).
Thanks for any Help!!!First off, if you are using the NewID() function, you will be generating Globally Unique Identifiers, not sequential numbers, so your ORDER BY ID DESC logic won't work.
Are you trying to get the most recent ID inserted? Try creating your ID as a variable within the trigger and then inserting it into both your tables. The NewID() function works well for this.
blindman|||Originally posted by blindman
First off, if you are using the NewID() function, you will be generating Globally Unique Identifiers, not sequential numbers, so your ORDER BY ID DESC logic won't work.
Are you trying to get the most recent ID inserted? Try creating your ID as a variable within the trigger and then inserting it into both your tables. The NewID() function works well for this.
blindman
Hi Blindman
NewID was just a variable for my thoughts, i don't use the function NewID - this is just the tablename and var-name of the trigger...
The idea is to have a main table with all the ID's and sub-data information so I can have a table Data1 Data2 and Data3 wich dont get themselves into the way with their ID's.|||If you are trying to keep IDs from being duplicated within or between data tables, then you should consider using the uniqueidentifier datatype and the NewID function. Its easy to use and will always give you a unique ID. I think it might solve your problem.
blindman|||Originally posted by blindman
If you are trying to keep IDs from being duplicated within or between data tables, then you should consider using the uniqueidentifier datatype and the NewID function. Its easy to use and will always give you a unique ID. I think it might solve your problem.
blindman
Wow, i've just took a look at this NewID function, that's a pretty big thing :-).
I would be pretty happy if i could handle the ID with numbers, not that a customer has to spell me those 20 chars :), do you know any way I could do this trough triggers?
Or is there a possibility to convert the NewID-var to some kind of number?|||NewID produces a standard UniqueIdentifier data type which can be implicitily converted to a char(36) or nchar(36) data type. It's good practice to make your IDs invisible to Users anyway and keep them for internal data processing.
blindman
Get Primary Key Column
Hello, this SQL query gets all columns of a table:
SELECT syscolumns.name AS [Fields in Items Database], syscolumns.type,syscolumns.length, syscolumns.isnullable FROM sysobjects INNER JOINsyscolumns ON sysobjects.id = syscolumns.id WHERE sysobjects.name ='IssueTracker_IssueAttachments' ORDER BY syscolumns.colid
I would like to know, is there a way to get a column saying who is theprimary key in the table ? I am reading columns for tables andprocessing them, but I would like to know who is the primary key, isthat possible ?
regards
Your query is running in the Master Database, you need to run it in the Issue Tracker database and to see the primary key of a table go to Query Analyzer and run a Select all query with show results as a grid. But most large databases uses Identity column for keys so the clustered index will be small. Hope this helps.|||
You can check a script listing Primary Key columns of a table by using this linkhttp://www.kodyaz.com/ShowPost.aspx?PostID=204
A simplified version is as below
declare @.tablename as sysname
set @.tablename = 'Customers'
declare @.tableid as int
select @.tableid = id from sysobjects where name = @.tablename
SELECT *
FROM syscolumns
INNER JOIN (
SELECT
*
FROM SysIndexKeys IK
WHERE
IK.Id = (select id from sysobjects where name = @.tablename)
AND IK.IndId = (select indid fromsysindexes where name = (select name from sysobjects where xtype = 'PK'and parent_obj = (select id from sysobjects where name = @.tablename)))
) PKColumns ON PKColumns.id = syscolumns.id AND PKColumns.colid = syscolumns.colid
I hope this helps
Eralper
http://www.kodyaz.com
|||Guys that was great, thanks a lot
I never used those system tables, are there any reference for them ? Are the SQL Server books good ?
thanks|||
Not exactly. the system tables are not removed. They are not tables anymore. They are just made as VIEWS now. So you can only do selectagainst them.
|||I did not say there are System tables in SQL Server 2005, I said the SQL Server 2000 poster was removed from the online location because SQL Server 2005 is almost here. I have known they are now views for a long time and I have always advised people not to use the System tables.
SELECT syscolumns.name AS [Fields in Items Database], syscolumns.type,syscolumns.length, syscolumns.isnullable FROM sysobjects INNER JOINsyscolumns ON sysobjects.id = syscolumns.id WHERE sysobjects.name ='IssueTracker_IssueAttachments' ORDER BY syscolumns.colid
I would like to know, is there a way to get a column saying who is theprimary key in the table ? I am reading columns for tables andprocessing them, but I would like to know who is the primary key, isthat possible ?
regards
Your query is running in the Master Database, you need to run it in the Issue Tracker database and to see the primary key of a table go to Query Analyzer and run a Select all query with show results as a grid. But most large databases uses Identity column for keys so the clustered index will be small. Hope this helps.|||
Well no, I am running this script against the IssueTrackerStarterKit database. I am getting that table for sure, listing al columns, but I would like to know if I can know the primary key programmatically !!
regards
|||Run a search for sp_helpindex in SQL Server BOL(books online). And your select statement is using SQL- DMO(data management object) which is Microsoft property and all service packs makes changes which will make you code out dated because the tables have moved. Go to Query Analyzer open the object browser and right click on your table and you will have options of select statements without Syscolumns and Sysobjects. Hope this helps.|||Hi,You can check a script listing Primary Key columns of a table by using this linkhttp://www.kodyaz.com/ShowPost.aspx?PostID=204
A simplified version is as below
declare @.tablename as sysname
set @.tablename = 'Customers'
declare @.tableid as int
select @.tableid = id from sysobjects where name = @.tablename
SELECT *
FROM syscolumns
INNER JOIN (
SELECT
*
FROM SysIndexKeys IK
WHERE
IK.Id = (select id from sysobjects where name = @.tablename)
AND IK.IndId = (select indid fromsysindexes where name = (select name from sysobjects where xtype = 'PK'and parent_obj = (select id from sysobjects where name = @.tablename)))
) PKColumns ON PKColumns.id = syscolumns.id AND PKColumns.colid = syscolumns.colid
I hope this helps
Eralper
http://www.kodyaz.com
|||Guys that was great, thanks a lot
I never used those system tables, are there any reference for them ? Are the SQL Server books good ?
thanks|||
The Systems tables have a poster but since SQL Server 2005 is almost here Microsoft have removed it and have made others to remove it. But they are all in the SQL Server Master database with the System title next to them. A good book for starting SQL Server for a developer is SQL Server a beginner's guide by Dusan Petkovic but don't let the title fool you it is not really a beginner's book it was given that title because English is not the writer's first language, he is German. The book covers everything usefull to a developer including complex configurations and the language, he also covered XML and Full text that most other books did not cover. I have a lot of them I call crappy but his book is worth the money for a C# developer, while SQL Server Developer's guide is good for VB developer. Try this link for T-SQL tutorial but I have sent you an ANSI SQL tutorial in the mail. Dowload the file because the site is now part of a consolidator so that file may be removed soon. Hope this helps.
http://www.mssqlserver.com/tsql/
|||
The Systems tables have a poster but since SQL Server 2005 is almosthere Microsoft have removed it and have made others to remove it. Butthey are all in the SQL Server Master database with the System titlenext to them.
Not exactly. the system tables are not removed. They are not tables anymore. They are just made as VIEWS now. So you can only do selectagainst them.
|||I did not say there are System tables in SQL Server 2005, I said the SQL Server 2000 poster was removed from the online location because SQL Server 2005 is almost here. I have known they are now views for a long time and I have always advised people not to use the System tables.
Get PK for inserted record in SQLdatasource
I have a table named invoice that contains the following columns
-invoiceno - Primary key and is set to autonumber
-customerno
-incoicedate
and on my VB code i did the following InsertCommand
SqlDataSource1.InsertCommand = "INSERT INTO invoice(customerno, invoicedate) VALUES('" & Session("UID") & "', GetDate()) "
SqlDataSource1.Insert()
My Question is how do i get the Primary Key Value it generated during the insert operation(invoice['incoiceno'])? Besides the creationg a stored procedere like the one in the MSDN Library
Well, since you really aren't using the sqldatasource as a datasource, just do it manually.
Dim conn as new sqlconnection("{Your connect string}")
conn.open
dim cmd as new sqlcommand("INSERT INTO invoice(customerno, invoicedate) VALUES(@.customerno,getdate()) SELECT @.newid=SCOPE_IDENTITY()",conn)
cmd.parameters.add("@.customerno",sqldbtype.uniqueidentifier).value=session("UID")
cmd.parameters.add("@.newid",sqldbtype.int).direction=output
cmd.executenonquery()
dim newid as integer=cmd.parameters("@.newid").value
conn.close
I believe you can also try to create a parameter on the insert with a direction of output as well, but I think you have to actually pull the value in sqldatasource1_inserted by referencing e.command.parameters("@.newid").value, but you can try and see if you can pull it directly after your insert too.
Subscribe to:
Posts (Atom)