sybase sql questions...

1). I have a EMP table -- I want to get all the rows which are equal .. what is the query for this?

select * from EMP group by col1, col2, col3, etc. having count(*) > 1

2). I want to get a employee which is 3rd in the salary range?

select name, salaryrank = identity(8) into #foo order by salary
select name from #foo where salaryrank = 3


3). I want to insert a column into this table which is a primary key and has distinct values (many be just count from 1......)

so basically

emp has

empName   empSalary

I want to insert a column

empId empName empSalary

and update table by populating empId column with distinct numbers...


ALTER TABLE emp add empId numeric(12,0) identity


4). In sybase -- there is something like internal deleted and inserted tables -- what are those? and how to read them?

These are internal views into the transaction log, they can only be accessed in triggers, but you
can select and join them as you would any other table.  inserted contains the new values for inserts and updates, deleted contains the old values for deletes and updates.

You normally wouldn't just select from them, but you can.  Usually you join them with the base table to identify the rows that were just affected by the command.

create trigger foo on emp for insert as select * from inserted
go


5). what are temp tables in sybase? where do we use them?

temp tables are used for scratch data.  They are specified by a "#" as the first character
of a table name.  Space for them is allocated in tempdb (or a user tempdb in recent versions).
They are automatically deallocated when the session (or procedure it was created in) exits.

I could not get
select name, salaryrank = identity(8) into #foo order by salary
select name from #foo where salaryrank = 3

to work..

this is my test script

select "CREATING TABLE"
go

IF OBJECT_ID('dbo.EMP') IS NOT NULL
    DROP TABLE dbo.EMP
go

CREATE TABLE dbo.EMP
(
    EmpName        varchar(30)     NOT NULL,
    EmpSalary   INT    NOT NULL,
)
LOCK DATAROWS
go
IF OBJECT_ID('dbo.EMP') IS NOT NULL
    PRINT '<<< CREATED TABLE dbo.EMP >>>'
ELSE
    PRINT '<<< FAILED CREATING TABLE dbo.EMP >>>'
go

select "select * from EMP"
go

select * from EMP
go

select "INSERTING VALUES"
go

INSERT INTO EMP
VALUES
("ABC",10000)
GO

INSERT INTO EMP
VALUES
("DEF",430000)
GO

INSERT INTO EMP
VALUES
("ABC",15000)
GO

INSERT INTO EMP
VALUES
("HNK",130000)

GO

INSERT INTO EMP
VALUES
("ABC",10000)
GO

select "select * from EMP"
go

select * from EMP
go

select "select * from EMP group by EmpName, EmpSalary having count(*) > 1"
go


select * from EMP group by EmpName, EmpSalary having count(*) > 1
go

select "select * from EMP group by EmpName having count(*) > 1"
go


select * from EMP group by EmpName having count(*) > 1
go

thanks!

it seems to be working fine,

could you please let me know how is it working..

what does identity(8) means here??

and it doesn't cover a scenario if

we have say 6 rows

emp salary
a       1
b       1
c       3
d       4
e       5
f        6

in this, basically third largest salary is for (d --- 4).

but I guess this query doesn't cover this scenario.