Hot answers tagged

3

DECLARE @t TABLE(InsertionDate datetime); INSERT @t(InsertionDate) VALUES(GETDATE()), ('20190125'); SELECT CONVERT(char(6), InsertionDate, 112) FROM @t; Result: 202004 201901


2

I'd like to somehow get the name of all columns in this new table. You can create a temporary view, which registers the row type. Then query either the information schema like you tried or the system catalog pg_attribute: CREATE TEMP VIEW tmp_v1 AS SELECT ...; -- your query here SELECT attname FROM pg_attribute WHERE attrelid = 'pg_temp.tmp_v1'::...


2

Not sure it is the best way but you could cast the date as a string and then use the "Right" to get only the month like so: declare @date datetime = getdate() select @date select MONTH(@date) select right(CONVERT(varchar(7),@date,102),2) If there is a lot of data, this will not be a performant way to do it. You could create a computed column that will ...


2

Just use COUNT(DISTINCT instead of COUNT( - because that’s essentially the difference between DENSE_RANK and RANK.


1

you can use generate_series() for that: insert into field (textfield, idcal) select '', c.idcal from calendar c where idcal = 42 cross join generate_series(1, c.numberoffields) as g(f)


1

You can get the information from yourcursor.description. Example: conn = psycopg2.connect(...) curs = conn.cursor() curs.execute("""SELECT person.given_name, ...""") curs.description You can find more info under metadata on zetcode.com/python/psycopg2/


1

Is this what you need? ( SELECT ... ) UNION ALL ( SELECT ... ) The GROUP BY on the first SELECT seems to be useless; please explain why you have it. I am less certain about the second query. Not sargable: DATEDIFF(NOW(),update_date) > 30; change to A.update_date > CURDATE() - INTERVAL 20 DAY. Recommend: A: INDEX(order_status, pending_order_type, ...


1

WITH cte AS ( SELECT *, ROW_NUMBER() OVER (ORDER BY StartTime) rn FROM schedules) UPDATE cte SET StartTime = DATEADD(minute, 15*(1-rn), StartTime); fiddle


1

Solution 1 The idea is to find distinct groups for each product, then the highest priority match calculated using a CASE expression: SELECT P.ProductId, P.ItemId, G.[Group], P.Color, V.[Value] FROM dbo.Products AS P CROSS APPLY ( -- Find groups for the current ItemId SELECT DISTINCT PP.[Group] FROM dbo.ProductPrices ...


1

I was also wondering if this was possible and stumbled on this post. I didn't do much research on the tablesample attribute, so technically the other answers are more correct to your direct question. However you can work around this if you really want to use the tablesample attribute by creating a temporary table (or similar) based on your conditional query....


1

SELECT * FROM wp_posts WHERE ID IN ( (SELECT courses FROM wp_category WHERE CatID =401) OR (SELECT meta_value FROM wp_postmeta WHERE post_id IN (SELECT courses FROM wp_category WHERE CatID =401) AND meta_key ='post_id' ) ) AND ID IN (SELECT post_id FROM wp_locations_courses WHERE location_id = 1120); Re-write those nasty subqueries to simply ...


Only top voted, non community-wiki answers of a minimum length are eligible