Select a specific staging record from sybase table

debugcn 发表于 Dev

Alpesh003

I have a table with data as below:

Id               stage              date             Emp_id3
=====        =====           =====              =====
DEP1          new             12thmarch         33
DEP1          approval        13thmarch         22
DEP1          reject          14thmarch         77
DEP1          approval        15thmarch         66
DEP1          reject          16thmarch         65
DEP1          approval        17thmarch         87
DEP1          complete        18thmarch         99

I want to find that Emp_id3 who has moved the staging data from reject to approval stage. In this case it will be 87 as it is the latest approval. This is the audit table that has stage wise data for its master table which has current stage data. Can some one please help me with the sybase query for the same? Any help will be much appreciated.

www

The problem you described hits cumber stone Sybase ase issue, no rank function. Bellow I have created a sample solution that involves using of temporary tables. Additional assumption is that date should be in format that ensures uniqueness ( DATETIME should be enough).

CREATE TABLE #temp
(
id VARCHAR(20),
stage VARCHAR(20),
DATE INT,
Emp_id3 INT)

INSERT INTO #temp
 select "DEP1", "new",    12,33
 union select "DEP1", "approval",13,22
 union select "DEP1", "reject", 14,77
 union select "DEP1", "approval",15,66
 union select "DEP1", "reject", 16,65
 union select "DEP1", "approval",17,87
 union select "DEP1", "complete",18,99

SELECT *, rank = identity(1) into #temp_rk FROM #temp ORDER BY DATE

SELECT changer.Emp_id3 FROM 
#temp_rk changer,#temp_rk originator
WHERE
    changer.stage="approval"
    AND originator.stage ="reject"
    AND changer.rank -1 = originator.rank
HAVING changer.rank = max(changer.rank)

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。

编辑于2021-06-10

我来说两句

0条评论

登录后参与评论

来自分类Dev

Related 相关文章

文章

Select a specific staging record from sybase table

Select a specific staging record from sybase table

Using PHP to select from an SQL table and removing a specific prefix from the results

SELECT FROM table,ORDER BY IF(...)

Select specific elements from a vector

iOS Select specific entries to display in table

PHP（“ SELECT * FROM Table WHERE ... AND ...”）

如何正确使用Sybase LOAD TABLE？

Python 2.7: Select specific character from string

LINQ select data from many table

SELECT * FROM NEW TABLE在Postgres中等效

Assign a value to select list (in a table) from a DB

SELECT * FROM MULTIPLE + DYNAMIC table_name

从SELECT * FROM $ table中排除字段

mysql select * from table group by id with rollup

MySQL SELECT FROM 3 Table with where

提高查询速度：简单的SELECT from SELECT in large table

select * from table where id = select max id in codeigniter

“ NOT IN table.col”和“ NOT IN SELECT col FROM table”之间的区别

How to select servers that does not have a date record in a separate table between a date range?

Sybase存储过程中的SELECT和TRUNCATE

Sybase和null字符串在select中。奇怪的处理

Select field from table A where subselect from table B = table A field

使用staging_table1和table2中的数据插入table1，同时使用staging_table1从table2中获取数据

使用staging_table1和table2中的数据插入table1，同时使用staging_table1从table2中获取数据

How do I copy data from each record of one table into multiple records of another table related to the first table?

MySQL SELECT * FROM table1，table2，table3

mysql insert into, select from other table while altering data

“ SELECT * FROM database..table”中的“ ..”是什么意思？

SELECT encrypted items from a table using LIKE on an expression

SELECT * FROM table WHERE MONTH =今天的下个月